CODEWITHSUNDEEP
sqlgoogle-cloud-platformgoogle-bigquery
user10664542
user10664542

Reputation: 1306

BigQuery - SQL date function, group by month spanning years

I have a field in a BigQuery table:

'created_date'

. I need to get output of the count of records by 'created_date' by month spanning years: 2014...2019 for example:

Desired output:

2014
Jan   1125
Feb   3308

2015
Jan   544
Feb   107
...

2016
...

2017

2018
...

2019
Jan   448
Feb   329
...

and so on.

or even:

Jan-2014  <count>
Feb-2014  <count>

anything will do, just an inclusive count (aggregation) of all records by month.

I found several ways to do this on Stack Overflow for Oracle, PostgreSQL and MySQL, but none of the approaches work with BigQuery.

Has anyone successfully done this with BigQuery? (and how). All responses, very much appreciated.

Upvotes: 4

Views: 3013

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

Below is for BigQuery Standard SQL

It assumes the created_date field is of DATE data type 

#standardSQL
SELECT 
  FORMAT_DATE('%b-%Y', created_date) mon_year, 
  COUNT(1) AS `count`
FROM `project.dataset.table`
GROUP BY mon_year
ORDER BY PARSE_DATE('%b-%Y', mon_year)

Above query will produce something like below 

Row mon_year    count    
1   Jan-2014    1389     
2   Feb-2014    1255     
3   Mar-2014    1655     
. . .   
60  Dec-2018    1677     
61  Jan-2019    1534     
62  Feb-2019    588

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269763

Use date_trunc():

select date_trunc(created_date, month)as yyyymm, count(*)
from t
group by yyyymm
order by yyyymm;

Upvotes: 1

Related Questions