英文:
SQL Count element and group on element
问题
我正在尝试创建一个视图，根据不同的元素对一个元素进行计数并进行分组。
我的表格大致如下：
| 源 | 材料 | 目的地 | |
|---|---|---|---|
| 1 | Src1 | A123456 | Dest2 |
| 2 | Src1 | A658979 | Dest1 |
| 3 | Src1 | B123456 | Dest1 |
| 4 | Src1 | B658979 | Dest1 |
| 5 | Src1 | C123456 | Dest2 |
我试图实现的目标描述如下：
将以"A"开头的材料计为"Count Material A"，以"B"开头的材料计为"Count Material B"，并按目的地分组。
基于上述表格，结果应该如下所示：
| Count Material A | Count Material B | 目的地 | |
|---|---|---|---|
| 1 | 1 | 2 | Dest1 |
| 2 | 1 | 0 | Dest2 |
目前，我只能成功创建计数部分的查询，但无法弄清楚如何基于目的地进行计数。
注意：我认为如果在 WHERE 子句中“硬编码”目的地，我知道如何做，但预计未来将有更多的目的地，所以我试图为这个解决方案做好未来的准备。
感谢您的帮助！
英文:
I am trying to create a view that counts an element and groups it based on a different element.
My table looks something like this
| Source | Material | Destination | |
|---|---|---|---|
| 1 | Src1 | A123456 | Dest2 |
| 2 | Src1 | A658979 | Dest1 |
| 3 | Src1 | B123456 | Dest1 |
| 4 | Src1 | B658979 | Dest1 |
| 5 | Src1 | C123456 | Dest2 |
Description of what I am trying to archive:
Count the materials that start with "A" as "Count Material A" and start with "B" as "Count Material B" and group by destination.
Based on the table above the result should look something like that:
| Count Material A | Count Material B | Destination | |
|---|---|---|---|
| 1 | 1 | 2 | Dest1 |
| 2 | 1 | 0 | Dest2 |
For now I am only able to succesfully create the query for the counting part but I cannot figure out how to base the counting on the destination as well.
NOTE: I think I know how to do it when I "hardcode" the destination in the where-clause but it is expected that there will be more destinations in the future so I am trying to future-proof this solution.
Thanks for the help!
答案1
得分: 3
我们可以在这里使用条件聚合：
SELECT
Destination,
COUNT(CASE WHEN Material LIKE 'A%' THEN 1 END) AS [Count Material A],
COUNT(CASE WHEN Material LIKE 'B%' THEN 1 END) AS [Count Material B]
FROM yourTable
WHERE Material LIKE 'A%' OR Material LIKE 'B%'
GROUP BY Destination;
英文:
We can use conditional aggregation here:
<!-- language: sql -->
SELECT
Destination,
COUNT(CASE WHEN Material LIKE 'A%' THEN 1 END) AS [Count Material A],
COUNT(CASE WHEN Material LIKE 'B%' THEN 1 END) AS [Count Material B]
FROM yourTable
WHERE Material LIKE 'A%' OR Material LIKE 'B%'
GROUP BY Destination;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论