年和月按代理商计算的销售额
年和月按代理商计算的销售额
提问于 2022-05-18 22:07:01
我有一张有agentID，transDate和transValue的桌子。我如何报告每个代理，年度和月份的所有transValue？这些列应该如下所示:代理to _每个月都必须对transValue进行总结。现在我有了一部分代码：
select agentID, year(transDate), sum(transValue) as January
from operations
where month(transDate) = 01
group by agentID, year(transDate)我不知道接下来的几个月该怎么做。有什么建议吗？
回答 1
Stack Overflow用户
发布于 2022-05-18 22:27:41
您可以使用条件聚合来生成报告-
select agentID, year(transDate),
sum(CASE WHEN month(transDate) = 1 THEN transValue END) as January,
sum(CASE WHEN month(transDate) = 2 THEN transValue END) as february,
sum(CASE WHEN month(transDate) = 3 THEN transValue END) as March,
sum(CASE WHEN month(transDate) = 4 THEN transValue END) as April,
sum(CASE WHEN month(transDate) = 5 THEN transValue END) as May,
sum(CASE WHEN month(transDate) = 6 THEN transValue END) as June,
sum(CASE WHEN month(transDate) = 7 THEN transValue END) as July,
sum(CASE WHEN month(transDate) = 8 THEN transValue END) as August,
sum(CASE WHEN month(transDate) = 9 THEN transValue END) as Spetember,
sum(CASE WHEN month(transDate) = 10 THEN transValue END) as October,
sum(CASE WHEN month(transDate) = 11 THEN transValue END) as November,
sum(CASE WHEN month(transDate) = 12 THEN transValue END) as December
from operations
group by agentID, year(transDate)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/72296530
复制相关文章
相似问题
腾讯云开发者
