SQL getting the sum of columns that have same name or id

Question

I have a table that needs to get the sum of the same name of the products (pt.name as sku) of the records.

My expected output is to be like this:

How can I do this? I already tried the subquery but I can't figure out on how to do it properly.

This is what my code look so far. When I only keep pt.name in GROUP BY this is my error message.

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


SELECT DISTINCT
                sol.order_id,
                so.name as so_number,
                DATE(so.requested_date + INTERVAL '8 HOURS') as delivery_date,
                us.name as sales_executive,
                rp.partner_type as account_type,
                rp.name as account_name,
                pt.name as sku,
                sol.price_unit as price,
                sum(distinct sol.product_uom_qty) as ordered_qty,
                sum(distinct sol.actual_delivered) as delivered_qty,
                CASE WHEN row_number() over (partition by sol.order_id, so.name order by DATE(so.requested_date + INTERVAL '8 HOURS')) = 1
                        THEN rb.return_qty
                END as return_qty,
                so.amount_total as gross_sales,
                ai.amount_untaxed as vatable_sales,
                ai.amount_tax as vat,
                ai.residual as net_sales
            FROM sale_order so
            LEFT JOIN res_partner rp ON rp.id = so.partner_id
            LEFT JOIN res_users ru ON ru.id = so.user_id
            LEFT JOIN res_partner us ON us.id = ru.partner_id
            LEFT JOIN sale_order_line sol ON sol.order_id = so.id
            LEFT JOIN product_template pt ON pt.id = sol.product_id
            LEFT JOIN account_invoice ai ON ai.origin = so.name
            LEFT JOIN return_bottle rb ON rb.sale_id = so.id
            WHERE DATE(so.requested_date + INTERVAL '8 HOURS') >= '2020-12-01' 
            AND DATE(so.requested_date + INTERVAL '8 HOURS') <= '2020-12-30' 
            AND ai.state != 'cancel'
            AND so.name = 'SO11157'
            GROUP BY sol.order_id, so.name, rb.return_qty, delivery_date, us.name, rp.partner_type, rp.name, pt.name, sol.price_unit, sol.product_uom_qty, sol.actual_delivered, so.amount_total, ai.amount_untaxed, ai.amount_tax, ai.residual
            ORDER BY delivery_date ASC, SOL.ORDER_ID

The highlighted values is the ordered_qty and delivered_qty

score 2 · Accepted Answer · answered Jan 29 '21 at 08:48

You should use SUM and GROUP by as follows:

SELECT 
                sol.order_id,
                so.name as so_number,
                DATE(so.requested_date + INTERVAL '8 HOURS') as delivery_date,
                us.name as sales_executive,
                rp.partner_type as account_type,
                rp.name as account_name,
                pt.name as sku,
                sum(sol.price_unit) as price,
                sum(distinct sol.product_uom_qty) as ordered_qty,
                sum(distinct sol.actual_delivered) as delivered_qty,
                sum(rb.return_qty) as return_qty,
                sum(so.amount_total) as gross_sales,
                sum(ai.amount_untaxed) as vatable_sales,
                sum(ai.amount_tax) as vat,
                sum(ai.residual) as net_sales
            FROM sale_order so
            LEFT JOIN res_partner rp ON rp.id = so.partner_id
            LEFT JOIN res_users ru ON ru.id = so.user_id
            LEFT JOIN res_partner us ON us.id = ru.partner_id
            LEFT JOIN sale_order_line sol ON sol.order_id = so.id
            LEFT JOIN product_template pt ON pt.id = sol.product_id
            LEFT JOIN account_invoice ai ON ai.origin = so.name
            LEFT JOIN return_bottle rb ON rb.sale_id = so.id
            WHERE DATE(so.requested_date + INTERVAL '8 HOURS') >= '2020-12-01' 
            AND DATE(so.requested_date + INTERVAL '8 HOURS') <= '2020-12-30' 
            AND ai.state != 'cancel'
            AND so.name = 'SO11157'
            GROUP BY sol.order_id, so.name, rb.return_qty, delivery_date, us.name, rp.partner_type, rp.name, pt.name
            ORDER BY delivery_date ASC, SOL.ORDER_ID

score 2 · Answer 2 · answered Jan 29 '21 at 12:51

If you want one row per "order id", then that should be the only column in the GROUP BY. So, your query should look like this:

SELECT so.order_id, so.name as so_number,
       DATE(so.requested_date + INTERVAL '8 HOURS') as delivery_date,
       MAX(us.name) as sales_executive,
       MAX(rp.partner_type) as account_type,
       MAX(rp.name) as account_name,
       -- pt.name as sku,  -- this doesn't make sense
       -- sol.price_unit as price, -- this doesn't make sense
       . . .
FROM sale_order so LEFT JOIN
     . . .
WHERE . . . 
GROUP BY so.order_id
ORDER BY delivery_date ASC, ORDER_ID;

Some of the columns don't make sense if you want one row per order. In particular, you can't really have detail on the items in the order if you want one row per order (unless you use JSON or arrays or something like that).

score 1 · Answer 3 · answered Jan 29 '21 at 08:53

1

To use sol.order_id in select list you need to have this in group by clause. Below link may answer better. Please check.

Select a Column in SQL not in Group By

answered Jan 29 '21 at 08:53

Kazi Mohammad Ali Nur Romel

15,301
2
13
26

SQL getting the sum of columns that have same name or id

3 Answers3