I have two CTEs that I’ve unioned two produce the following result:
x
date serie events
2019-05-09 hello 175
2019-05-02 hello 196
2019-05-09 goodbye 1
2019-05-02 goodbye 1
I want to add the events per day together so that
2019-05-09 total 176
2019-05-02 total 197
At the moment I have something like:
with hello_events as (
--- actual logic not interesting ---
)
, goodbye_events as (
--- actual logic not interesting ---
)
select * from hello_events
union all
select * from goodbye_events
If I want a third set of results being the sum, I assume I need to do something like
with hello_events as (
--- actual logic not interesting ---
)
, goodbye_events as (
--- actual logic not interesting ---
)
select * from hello_events
union all
select * from goodbye_events
union all
select date, "total", sum(events) as events
from hello_events, goodbye_events
where hello_events.date = goodbye_events.date
But that’s clearly not correct. What am I missing?
Advertisement
Answer
Just group by
, as below:
with hello_events as (
--- actual logic not interesting ---
)
,goodbye_events as (
--- actual logic not interesting ---
)
,tmp_result as (
select * from hello_events
union all
select * from goodbye_events
)
select
date,
'total',
sum(events)
from
tmp_result
group by
date