Learning Google BigQuery

ROLLUP

The ROLLUP function is used to perform aggregation at multiple levels. The following query returns the number of trips made per month by the company specified in the where clause, and it also shows the total trips for each year and the total trips made in all the years available:

#legacySQLSELECT STRFTIME_UTC_USEC(trip_start_timestamp,'%Y') as trip_year,STRFTIME_UTC_USEC(trip_start_timestamp,'%Y-%m') as trip_year_month,company,count(1) as trip_countFROM [bigquery-public-data:chicago_taxi_trips.taxi_trips] WHERE company = '1085 - N and W Cab Co'GROUP BY ROLLUP(company, trip_year, trip_year_month)order by company, trip_year, trip_year_month