CODEWITHSUNDEEP
sqlgoogle-bigquery
J. Ayo
J. Ayo

Reputation: 560

Shorthand for SQL query to count records by day across many tables

I am trying to create a table to validate my data across many different tables within a dataset. I use the following SQL query to do this, but is there any way to save time copying in the names of all tables within my dataset, either in the top level SELECT statement or the sub queries?

SELECT Date, records_table1, records_table2...

FROM

(SELECT RowDate as Date, Count(RowDate) AS records_table1 FROM [project:dataset.table1] GROUP BY Date),

(SELECT RowDate as Date, Count(RowDate) AS records_table2 FROM [project:dataset.table2] GROUP BY Date),

...

Upvotes: 0

Views: 229

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can also use union all :

select RowDate, sum(table1) as records_table1, sum(table2) as records_table2
from (select RowDate, 1 as table1, 0 as table2
      from [project:dataset.table1] 
      union all
      select RowDate, 0, 1
      from [project:dataset.table2] 
     ) t
group by RowDate;

Upvotes: 1

Daniel Marcus
Daniel Marcus

Reputation: 2686

select a.*, b.records_table2 from 
(SELECT RowDate as Date, Count(RowDate) AS records_table1 FROM [project:dataset.table1] GROUP BY Date)a
full join 
(SELECT RowDate as Date, Count(RowDate) AS records_table2 FROM [project:dataset.table2] GROUP BY Date)b 
on a.[Date]=b.[Date]

Upvotes: 1

Related Questions