Adding SQL Rows from two CTEs

I have two CTEs that I’ve unioned two produce the following result:

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?

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

Advertisement

Answer