包括使用group by的所有仓库的所有产品的总数

2hh7jdfx 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(230)

我有以下查询来获取每个仓库中每个产品的总数量：

SELECT 
    warehouse, product, SUM(quantity)
FROM
    inventory
GROUP BY warehouse, product

这是我目前的结果：

warehouse       product 
    San Fransisco   iPhone  260
    San Fransisco   Samsung 300
    San Jose        iPhone  300
    San Jose        Samsung 350

我想包括每个仓库的产品总数。这是我的预期输出：

warehouse       product 
    San Fransisco   iPhone  260
    San Fransisco   Samsung 300
    San Fransisco   Total   560
    San Jose        iPhone  300
    San Jose        Samsung 350
    San Jose        Total   650
    All warehouses  Total   1210

sql sql-server tsql

来源：https://stackoverflow.com/questions/62533580/include-total-of-all-products-for-all-warehouses-using-group-by

2条答案

按热度按时间

k2fxgqgv1#

SELECT 
    ISNULL(warehouse,'All warehouses') AS warehouse
    , ISNULL(product, 'Total') AS product, SUM(quantity) AS quantity
FROM
    inventory
GROUP BY ROLLUP (warehouse , product);

赞(0）回复(0）举报 2021-07-24

gfttwv5a2#

一种方法使用 grouping sets :

select warehouse, product, sum(quantity)
from inventory
group by grouping sets ( (warehouse, product), (warehouse), () );

鉴于that:you have 不 NULL 可以使用的键中的值 coalesce() 要获取总计：

select coalesce(warehouse, 'Total') as warehouse,
       (case when warehouse is not null
             then coalesce(product, 'Total')
        end) as product,
       sum(quantity)
from inventory
group by grouping sets ( (warehouse, product), (warehouse), () );

这个 product 是很棘手的，因为有时是这样 NULL ，有时 Total ，有时 product .

赞(0）回复(0）举报 2021-07-24

我来回答

包括使用group by的所有仓库的所有产品的总数

2条答案

相关问题

热门标签

最新问答