I have the following Table charges
x
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|
Using the following query gets my the counts by Charge and Date
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|
Is there a way to transpose this to get the following?
Expected:
|Timestamp|Yearly|Monthly|
|---------+------+-------|
|8/01/2020| 1| 3|
|8/02/2020| 2| 2|
Thank you.
Advertisement
Answer
Use conditional aggregation:
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