CODEWITHSUNDEEP
sqlgoogle-bigquery
J. Ayo
J. Ayo

Reputation: 560

Count rows by date on multiple tables within a Big Query database using Legacy SQL

I use the following SQL query to extract the number of rows in a table (table1) grouped by the RowDate:

SELECT RowDate AS Date,

Count(RowDate) as NumberRows FROM [project:dataset.table1] GROUP BY Date ORDER BY Date

This gives me a table that shows:

Row Date    NumberRows   
1   2017-01-01  54   
2   2017-01-02  57   
3   2017-01-03  46   
4   2017-01-04  32   
5   2017-01-05  28

Is it possible to include multiple tables within my data set (or even better at project level) within this query, to show a separate column for the count of rows for each RowDate (a common column across all tables)?

If so, can this be done without naming the tables explicitly within the query?

Any help would be much appreciated.

Thank you.

Upvotes: 0

Views: 2312

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

If for some reason you still work with BigQuery Legacy SQL - you should use TABLE_QUERY() for this - see an example below 

#legacySQL
SELECT 
  RowDate,
  COUNT(1) AS NumberRows 
FROM TABLE_QUERY([project:dataset], 'true')
GROUP BY RowDate 
ORDER BY RowDate

Meantime, check out - Wildcard Tables - features of BigQuery Standard SQL - and Migrating to Standard SQL

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269483

Using standard SQL, you can do something like this:

SELECT RowDate AS Date,
select row_date, sum(t1_rows) as t1_rows, sum(t2_rows) as t2_rows, . . .
from ((select rowdate, count(*) as t1_rows, 0 as t2_rows, . . .
       from table1
       group by row_date
      ) union all
      (select rowdate, 0, count(*), . . .
       from table2
       group by row_date
      ) union all
      . . . 
     ) t
group by rowdate
order by rowdate;

Upvotes: 3

Related Questions