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 (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
    


  • 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:

    • https://dba.stackexchange.com/questions/173831/convert-right-side-of-join-of-many-to-many-into-array/173879#173879

    About the aggregate FILTER clause:

    • https://dba.stackexchange.com/questions/93510/return-counts-for-multiple-ranges-in-a-single-select-statement/93522#93522



Suggested Topics

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