adding all columns from mutiple tables

Question

I have a simple question.

I need to count all records from multiple tables with day and hour and add all of them together in a single final table.

So the query for each tab is something like this

select timestamp_trunc(timestamp,day) date, timestamp_trunc(timestamp,hour) hour, count(*) from table_1

select timestamp_trunc(timestamp,day) date, timestamp_trunc(timestamp,hour) hour, count(*) from table_2

select timestamp_trunc(timestamp,day) date, timestamp_trunc(timestamp,hour) hour, count(*) from table_3

and so on so forth

I would like to combine all the results showing number of total records for each day and hour from these tables.

Expected results will be like this

date, hour, number of records of table 1, number of records of table 2, number of records of table 3 ........

What would the most optimum SQL query for this?

Gordon Linoff Gordon Linoff · Accepted Answer · 2020-03-18T00:11:05

Probably the simplest way is to union them together and aggregation:

select timestamp_trunc(timestamp, hour) as hh,
       countif(which = 1) as num_1,
       countif(which = 2) as num_2
from ((select timestamp, 1 as which
       from table_1
      ) union all
      (select timestamp, 2 as which
       from table_2
      ) union all
      . . .
     ) t
group hh
order by hh;

You are using timestamp_trunc(). It returns a timestamp truncated to the hour -- there is no need to also include the date.

adding all columns from mutiple tables

2 Answers