Aggregating data in BigQuery by month

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