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

question from:https://stackoverflow.com/questions/65951249/sql-getting-the-sum-of-columns-that-have-same-name-or-id

Answer 1

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