Aggregating data in BigQuery by month

Tags: ,



I feel silly asking this because it seems simple, but I concede and need some help. In BigQuery I have a Table that looks like this:

Date Portfolio Super_Discipline Dollars Units
2020-05-20 Mathematics Precalculus 546.99 46
2020-01-06 Mathematics Calculus 232.80 27
2019-08-10 Professional IT 53.99 19
2019-01-15 Science Biol 862.24 99

What I’m trying to accomplish is groupby month. So I’m looking to create a table that has each portfolio and super discipline and month (date) uniquely in a row with the sales and units for that month and those attributes summed up.

If it helps, if I was in Python I would simply replace the Date column with the months and then type df.groupby(['Portfolio','Super_Discipline']).sum().reset_index()

I think my biggest issue is that the date column is in days, so when I convert it with “FORMAT_DATE(‘%Y-%m’, Date) AS Date” I can’t groupby it. Thanks in advance.

Answer

Use below

select format_date('%Y-%m', Date) as month, 
 Portfolio, 
 Super_Discipline, 
 sum(Dollars) as Dollars, 
 sum(Units) as Units
from your_table
group by month, Portfolio, Super_Discipline


Source: stackoverflow