how to use distinct on to avoid aggregating column in this query?

  • QA Engineer

    what I am trying to do is I want to calculate the quantity for the inventory the formula for that is receive_order_entries.quantity+ the sum of all inventory_transactions.quantity for that inventory

    my current approach are like below but it has problem with grouping and aggregating

    how to avoid using aggregate or group by for each column that I select in this query?

    I know the column is should be aggregated or grouped by since inventory has many inventory transactions

    I have read about distinct on from postgres sql but I don't know how to use it I have tried to add DISTINCT ON (,receive_order_entries.product_id) after select but it still gives the same error messages

    the error messages

    ERROR:  column "" must appear in the GROUP BY clause or be used in an aggregate function

    my db schema from rails

    # in rails every table automatically
     has autoincrement bigint id for primary key, and datetime for created_at

    create_table "receive_order_entries", force: :cascade do |t|
    t.bigint "product_id", null: false
    t.datetime "expiry_date"

    create_table "products", force: :cascade do |t|
    t.string "nama", default: "", null: false

    create_table "inventories", force: :cascade do |t|
    t.bigint "receive_order_entry_id", null: false
    t.decimal "harga_jual", default: "0.0", null: false

    create_table "inventory_transactions", force: :cascade do |t|
    t.bigint "inventory_id", null: false
    t.string "type"
    t.integer "quantity", default: 0, null: false

    my current sql syntax

    select, as product_id, as receive_order_entry_id,
    sum(receive_order_entries.quantity + 
            when inventory_transactions.type = 'DecrementInventoryTransaction' then -(inventory_transactions.quantity)
            when inventory_transactions.type = 'IncrementInventoryTransaction' then inventory_transactions.quantity
            else 0 end
            FROM inventory_transactions
            WHERE inventory_transactions.inventory_id =
            and not inventory_transactions.approved_at is NULL
            and not inventory_transactions.approved_by_id is NULL),0)
       ) as qty
    from inventories 
    inner join receive_order_entries 
    on = 
    inner join products
    on receive_order_entries.product_id = 
    group by,,

  • Filling in for a couple of inconsistencies in the question, this should do it:

    SELECT, r.product_id, i.receive_order_entry_id, p.nama, r.expiry_date, i.harga_jual
         , r.quantity + it.qty AS qty
    FROM   inventories i
    JOIN   receive_order_entries r ON = i.receive_order_entry_id
    JOIN   products              p ON = r.product_id
    LEFT   JOIN (
       SELECT inventory_id
            , COALESCE(sum(quantity) FILTER (WHERE type = 'IncrementInventoryTransaction'), 0)
            - COALESCE(sum(quantity) FILTER (WHERE type = 'DecrementInventoryTransaction'), 0) AS qty
       FROM   inventory_transactions
       WHERE  approved_at IS NOT NULL
       AND    approved_by_id IS NOT NULL
       GROUP  BY 1
       ) it ON it.inventory_id =;

    The point is to aggregate quantities from inventory_transactions in a subquery before joining to it. That's also the fastest way while processing the whole or most of the table. For a small selection use a LATERAL subquery instead. See:


    About the aggregate FILTER clause:


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2