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

Question

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 (inventories.id,receive_order_entries.product_id) after select but it still gives the same error messages

the error messages

ERROR:  column "products.id" 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"
end
create_table "products", force: :cascade do |t|
  t.string "nama", default: "", null: false
end
create_table "inventories", force: :cascade do |t|
  t.bigint "receive_order_entry_id", null: false
  t.decimal "harga_jual", default: "0.0", null: false
end
create_table "inventory_transactions", force: :cascade do |t|
  t.bigint "inventory_id", null: false
  t.string "type"
  t.integer "quantity", default: 0, null: false
end

my current sql syntax

select inventories.id, products.id as product_id, receive_order_entries.id as receive_order_entry_id,
products.nama,
receive_order_entries.expiry_date,
inventories.harga_jual,
sum(receive_order_entries.quantity + 
   COALESCE((SELECT SUM(case 
        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 = inventories.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 inventories.id = receive_order_entries.id 
inner join products
on receive_order_entries.product_id = products.id 
group by inventories.id, products.id, receive_order_entries.id

Erwin Brandstetter · Accepted Answer · 2022-02-05T15:34:30.730

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

SELECT i.id, 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 r.id = i.receive_order_entry_id
JOIN   products              p ON p.id = 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 = i.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:

Convert right side of join of many to many into array

About the aggregate FILTER clause:

Return counts for multiple ranges in a single SELECT statement

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

1 Answers1