How to group data by month with BigQuery
When working with data, it's common to aggregate by month. Whether you're analyzing past trends or making forecasts, grouping by months allows for easier comparison between time periods, as well as a better understanding of future patterns.
There are different format options when it comes to grouping data by month with SQL:
- Group by month number:
- Group by month number year:
04 2023for April 2023
- Group by month year:
Grouping by month year is the most intuitive way to present your data. And that's especially true when working with non-technical stakeholders.
Yet, BigQuery does not have any
YEAR() functions. Instead, you have to use the following functions:
SELECTstatement to format the date as a string using the month year format.
ORDER BYclause to convert the string back into a date format and then sort the result in chronological order.
Here's how the query should look like if you want to aggregate by month using the month year format (ie:
SELECT FORMAT_DATE('%B %Y', DATE (date_column)) AS month, column_2, column_3 FROM table GROUP BY month ORDER BY PARSE_DATE('%B %Y', month)
And if you want to aggregate by month using the month abbreviation year format (ie:
SELECT FORMAT_DATE('%b %Y', DATE (date_column)) AS month, column_2, column_3 FROM table GROUP BY month ORDER BY PARSE_DATE('%b %Y', month)
Using this format allows stakeholders to quickly understand the story your data tell. Remember that if you want to drive business impact, your work should be as inclusive and accessible as possible. And presenting your results in a clear and meaningful way is a crucial part of that.