Groupby with Monthly Totals? How to Aggregate

Grouping data by month and calculating totals is a common data-analysis task. You take a dataset with dates, extract the year and month from each date, and then sum (or average, count, etc.) the values for each month. This article explains how to do this in popular tools like Excel, Google Sheets, SQL, and Python, with examples and common pitfalls.

Formula

The core calculation for a monthly total is simply the sum of all values whose date falls within a given month. In a formula, for a single month:
Monthly Total = SUM(values where year = Y and month = M)
Where Y is the year (e.g., 2024) and M is the month number (1-12). The average per day, if needed, is Monthly Total / number of days in that month. The number of days varies (28-31), so use the actual count for the specific month, not a fixed 30.

Worked Example

Suppose you have 100 minutes of activity spread over a month with 30 days. Using the formula above:

1. Monthly Total = 100 minutes (already given).
2. Days in month = 30.
3. Average per day = 100 / 30 = 3.33 minutes per day.

The calculator above gives the same result: 3.33 min/day. This simple arithmetic is the basis for any monthly aggregation – you just need to identify which rows belong to each month first.

Why Group by Month?

Monthly totals reveal trends that daily data hides. A retail store might see daily sales fluctuate wildly, but monthly totals show a clear growth pattern. An analyst tracking website traffic might compare monthly unique visitors to spot seasonal peaks. Grouping by month smooths out daily noise and makes long-term patterns visible. It also aligns with standard business reporting periods (monthly financial statements, marketing reports, etc.).

How to Group by Month in Excel and Google Sheets

Both Excel and Google Sheets handle this with a pivot table or formulas. The quickest method is a pivot table:

1. Select your data (including the date column and the value column).
2. Insert a Pivot Table (Excel: Insert > PivotTable; Sheets: Insert > Pivot table).
3. Drag the date field to the Rows area. Right-click the date and select “Group” (Excel) or “Create pivot date group” (Sheets). Choose “Months” (and “Years” if your data spans multiple years, to avoid mixing January 2023 with January 2024).
4. Drag the value field to the Values area. It will default to Sum; change it to Average, Count, etc. as needed.

Alternatively, you can use a formula. In Excel, if your dates are in column A and values in column B, you can create a helper column with =TEXT(A2,"YYYY-MM") to get a year-month text like “2024-07”. Then use SUMIF based on that helper column. In Google Sheets, the same approach works. This is useful when you want a formula-driven report rather than a pivot table.

How to Group by Month in SQL

In SQL, you use the GROUP BY clause with a date function. The exact function varies by database:

• PostgreSQL/MySQL: DATE_TRUNC('month', date_column) or DATE_FORMAT(date_column, '%Y-%m')
• SQL Server: FORMAT(date_column, 'yyyy-MM') or DATEPART(year, date_column), DATEPART(month, date_column)
• SQLite: strftime('%Y-%m', date_column)

A typical query looks like:

SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS total
FROM orders
GROUP BY month
ORDER BY month;

Always group by the truncated date (or the year and month separately) rather than by the full timestamp, otherwise each unique timestamp becomes its own group.

How to Group by Month in Python (Pandas)

In Python with pandas, you first ensure your date column is in datetime format, then set it as the index or use resample. The simplest method:

import pandas as pd
df['date'] = pd.to_datetime(df['date'])
df.set_index('date', inplace=True)
monthly = df['value'].resample('M').sum()  # 'M' for month-end frequency

If your data spans multiple years, use resample('M') which automatically groups by year-month. If you prefer a string column, use df.groupby(df['date'].dt.to_period('M'))['value'].sum(). The dt.to_period('M') method creates a period object like ‘2024-07’ that groups correctly across years.

Common Mistakes and How to Avoid Them

• Ignoring the year: If you group only by month number (1-12), January 2023 and January 2024 will be combined. Always include the year in the grouping key.
• Using text dates: If your date column is text, the grouping may sort alphabetically (e.g., “10” before “2”). Convert to a real date type first.
• Time zone issues: In SQL and Python, dates might be stored in UTC. If you need local months, convert the time zone before grouping.
• Empty months: Some tools (like pandas resample) will include months with no data as 0 or NaN. Decide whether you want to fill them or drop them.
• Month length: When calculating daily averages, use the actual number of days in that month (e.g., 30 for April, 31 for May, 28 for February). Using a fixed 30 will skew results.

When to Use a Pivot Table vs. Formula vs. Code

Choose the tool based on your workflow and data size:

• Pivot table: Best for interactive analysis in Excel/Sheets. Quick to create, easy to change the aggregation (sum, average, count).
• Formulas (SUMIF, etc.): Good when you need a reusable, transparent calculation in a spreadsheet. They update automatically when source data changes.
• SQL: Ideal when the data lives in a database and you need to aggregate large datasets efficiently. It’s also reproducible for reporting.
• Python/pandas: Best for complex transformations, automation, or when you’re already working in a data science environment. It handles large data well and integrates with visualization libraries.

Frequently Asked Questions

How do I group by month in Excel without a pivot table?

Create a helper column with the formula =TEXT(A2,"YYYY-MM") (assuming dates in A). Then use SUMIF with that helper column as the criteria range. For example, =SUMIF(C:C, "2024-07", B:B) sums all values in B where the helper column equals that month.

Why is my SQL GROUP BY month mixing years?

You’re likely grouping by the month number only (e.g., MONTH(date)). Use DATE_TRUNC('month', date) or group by both year and month: GROUP BY YEAR(date), MONTH(date).

How do I calculate a monthly average from daily data?

First group by month and sum the daily values, then divide by the number of days in that month (or by the number of data points if you have missing days). In SQL, you can use AVG(amount) over the month group, but that averages per row, not per day – be clear which you need.

What is the difference between resample(‘M’) and resample(‘MS’) in pandas?

'M' is month-end frequency, labeling the group with the last day of the month (e.g., 2024-07-31). 'MS' is month-start, labeling with the first day (2024-07-01). The values are the same; only the label differs.

Can I group by month in Google Sheets using a query?

Yes, use the QUERY function: =QUERY(A:B, "select year(A), month(A)+1, sum(B) where A is not null group by year(A), month(A)+1 label year(A) 'Year', month(A)+1 'Month', sum(B) 'Total'"). Note that month(A) returns 0 for January, so add 1.

How do I handle empty months in a monthly total chart?

In pandas, use resample('M').sum().fillna(0) to fill missing months with 0. In Excel, a pivot table automatically includes all months if you have a continuous date range; otherwise, you may need to fill gaps manually or use a date table.

Related

• Beginner Dual Booting Mistakes: 7 to Avoid
• Dependent Dropdown Lists: How They Work
• Marvel Rivals Ranks: Full Tier List & How It Works

in t | Total minutes | min | 100 | 0
in d | Days in month | days | 30 | 1 | 31
out avg | Average per day | min/day | t / d | 2