0

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

1 Answer 1

0

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:

About the aggregate FILTER clause:

3
  • I've update my question with my own working solutions have you any thoughts for that?
    – joyoy
    Feb 5, 2022 at 7:25
  • hi I've update my question with my own working solutions with group by inventories.id, products.id, receive_order_entries.id, you seems experience any thoughts about ur solutions vs using group by ?
    – joyoy
    Feb 5, 2022 at 9:56
  • @joyoy: Hard to say. Depends on the exact table definitions and cardinalities. (receive_order_entries.quantity does not even show in your schema, and I see no FK constraints) Typically, my query is the fastest option. Feb 5, 2022 at 15:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.