SQL Server How to group multiple columns data

mhd8tkvw 于 2023-05-28 发布在其他

关注(0)|答案(1)|浏览(125)

In the below table I want to group the product name then sum the quantity to be under each Model name.

tblStore

ID	Product	Model	Qty
1	Banana	M1	1.00
2	Apple	M3	0.50
3	Pawpaw	M3	1.50
4	Orange	M2	2.00
5	Banana	M1	1.00
6	Orange	M1	0.50

Select Product, 
    Sum(Case When tblStore.Quantity  = 'M1' Then 1 Else 0 End) As M1,
    Sum(Case When tblStore.Quantity  = 'M2' Then 1 Else 0 End) As M2,
    Sum(Case When tblStore.Quantity  = 'M3' Then 1 Else 0 End) As M3
From tblStore
Group by Name

Expected output

Product	M1	M2	M3
Banana	2.0
Apple			0.5
Pawpaw			1.5
Orange	0.5	2.0

sql-server

来源：https://stackoverflow.com/questions/76326778/how-to-group-multiple-columns-data

1条答案

按热度按时间

jdzmm42g1#

You have your column references a bit backwards, you are after the following:

select Product, 
 Sum(case when Model = 'M1' then Qty else 0 end) M1,
 Sum(case when Model = 'M2' then Qty else 0 end) M2,
 Sum(case when Model = 'M3' then Qty else 0 end) M3
from tblStore 
group by Product;

赞(0）回复(0）举报 2023-05-28

我来回答

SQL Server How to group multiple columns data

tblStore

1条答案

相关问题

热门标签

最新问答