英文:
How to group multiple columns data
问题
以下是翻译好的部分：
在下表中，我想将产品名称分组，然后将数量总和放在每个型号名称下。
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 Product
期望的输出
| Product | M1 | M2 | M3 |
|---|---|---|---|
| Banana | 2.0 | ||
| Apple | 0.5 | ||
| Pawpaw | 1.5 | ||
| Orange | 0.5 | 2.0 |
英文:
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 |
答案1
得分: 0
你的列引用有点颠倒，你想要的是以下内容：
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;
英文:
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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论