How to GROUP BY Month-Year using BigQuery

user_14273536 0
Viewed 2989

I am trying to count the number of bus trips (with start and destinations) on monthly basis (for several years) using TIMESTAMP column/field. I can do this on MONTH basis (TIMESTAMP_TRUNC(start_date, MONTH)) but I would like to do this for MONTH-YEAR basis. Any help is appreciated. Thanks

google-bigquery sql
2 Answers

you can use Standard SQL:

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)

if you are using timestamp you have to cast it to date

SELECT
  FORMAT_DATE('%b-%Y', DATE(CURRENT_TIMESTAMP())) mon_year

will produce:

Sep-2020

As per your example from comments. You can't use count in where clause. If you want to have a filter on aggregation you have to use having docs.

SELECT TIMESTAMP_TRUNC(start_date, MONTH) AS year_month,
       start_station_name,
       end_station_name,
       count(start_station_name) AS count_start,
FROM bigquery-PUBLIC-data.san_francisco.bikeshare_trips
WHERE start_station_name <> end_station_name
GROUP BY year_month,
         start_station_name,
         end_station_name
HAVING count(start_station_name) > 10
LIMIT 50
user_103271890

Your code should do what you want:

select timestamp_trunc(start_date, month) as yyyymmm, count(*)
from t
group by yyyymm;

This includes both the year and month, so Jan 2020 is different from Jan 2019.

If you wanted just by month of the year, then use extract():

select extract(month from start_date) as mon, count(*)
from t
group by mon;

This would treat Jan 2020 as the same as Jan 2019.

user_11440350
Related