SQL获取具有相同名称或ID的列的总和

安德里温·马昆托

我有一个表需要获取记录的同名产品（pt.name as sku）的总和。

我的预期输出是这样的：

我怎样才能做到这一点？我已经尝试过子查询，但我不知道如何正确执行。

这是我的代码到目前为止的样子。当我只保留 pt.name 时，GROUP BY这是我的错误消息。

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

突出显示的值是ordered_qty 和delivered_qty

大力水手

您应该使用SUM和GROUP by如下：

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

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。