sql按转置分组

fcwjkofz 于 2021-07-26 发布在 Java

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

我有下表的费用

charges:
|Quantity|Timestamp| Charge|
|--------+---------+-------|
|       1|8/01/2020| Yearly|
|       2|8/01/2020|Monthly|
|       1|8/01/2020|Monthly|
|       2|8/02/2020| Yearly|
|       1|8/02/2020|Monthly|
|       1|8/02/2020|Monthly|

使用下面的查询可以按费用和日期获取计数

Query:
select SUM(Quantity), Timestamp, Charge 
from charges 
group by Timestamp, Charge 

Result:
|Sum|Timestamp| Charge|
|---+---------+-------|
|  1|8/01/2020| Yearly|
|  3|8/01/2020|Monthly|
|  2|8/02/2020| Yearly|
|  2|8/02/2020|Monthly|

有没有一种方法可以把这个转换成下面的？

Expected:
|Timestamp|Yearly|Monthly|
|---------+------+-------|
|8/01/2020|     1|      3|
|8/02/2020|     2|      2|

谢谢您。

sql group-by sum pivot

来源：https://stackoverflow.com/questions/63199735/sql-group-by-transpose

2条答案

按热度按时间

hgncfbus1#

使用条件聚合：

select
    timestamp,
    sum(case when charge = 'Yearly'  then charge else 0 end) yearly,
    sum(case when charge = 'Monthly' then charge else 0 end) monthly
from charges
group by timestamp

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

sshcrbum2#

根据您的rdbms，您可以使用 PIVOT 功能。

SELECT *
  FROM (SELECT QUANTITY, TIMESTAMP, CHARGE
          FROM CHARGES C) C PIVOT (SUM (QUANTITY)
                            FOR CHARGE
                            IN ('Yearly', 'Monthly'))

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

我来回答

sql按转置分组

2条答案

相关问题

热门标签

最新问答