Revenue grouped by month is probably the most requested query in any company, and it is also the one most often quietly wrong. The grouping itself is easy. What catches people out is everything around it.
This guide gives you the query for both engines, then covers the three mistakes that produce a number nobody notices is incorrect.
The query
Assume an orders table with a created_at timestamp and a total amount.
PostgreSQL
SELECT
DATE_TRUNC('month', created_at) AS month,
SUM(total) AS revenue,
COUNT(*) AS orders
FROM orders
WHERE created_at >= DATE_TRUNC('year', CURRENT_DATE)
GROUP BY 1
ORDER BY 1;DATE_TRUNC('month', ...) rounds every timestamp down to the first instant of its month, so every order in March collapses to the same value and groups together. The GROUP BY 1 refers to the first column in the select list, which saves repeating the expression.
MySQL
MySQL has no DATE_TRUNC. The usual equivalent is formatting the date down to month precision:
SELECT
DATE_FORMAT(created_at, '%Y-%m-01') AS month,
SUM(total) AS revenue,
COUNT(*) AS orders
FROM orders
WHERE created_at >= MAKEDATE(YEAR(CURDATE()), 1)
GROUP BY month
ORDER BY month;Formatting to '%Y-%m-01' rather than '%Y-%m' keeps the value sortable as a date rather than a string, which matters as soon as you cross a year boundary.
Three things that make the number wrong
Months with no orders disappear
A GROUP BY can only return months that exist in the data. If March had no orders, there is no March row, and a chart drawn from that result silently joins February to April. Nobody notices until someone counts the bars.
The fix is to generate the full range of months and left join onto it. In PostgreSQL:
SELECT
m.month,
COALESCE(SUM(o.total), 0) AS revenue
FROM generate_series(
DATE_TRUNC('year', CURRENT_DATE),
DATE_TRUNC('month', CURRENT_DATE),
INTERVAL '1 month'
) AS m(month)
LEFT JOIN orders o
ON DATE_TRUNC('month', o.created_at) = m.month
GROUP BY m.month
ORDER BY m.month;MySQL has no generate_series, so the usual approach is a small calendar table, or a recursive CTE on version 8.0 and above.
The month boundary depends on the time zone
If created_at is stored in UTC and your company works in Madrid, an order placed at 00:30 on 1 March local time is stored as 23:30 on 28 February. Group by the raw column and that revenue lands in the wrong month. At the end of a quarter, this is the kind of thing that moves a reported figure.
Convert before grouping:
-- PostgreSQL
DATE_TRUNC('month', created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Madrid')
-- MySQL
DATE_FORMAT(CONVERT_TZ(created_at, '+00:00', 'Europe/Madrid'), '%Y-%m-01')Refunds and cancellations are still counted
SUM(total) adds up every row in the table, including orders that were cancelled, refunded or never paid for. Whether that is correct depends on what you were asked for, and the two numbers can differ by a lot.
SELECT
DATE_TRUNC('month', created_at) AS month,
SUM(total) FILTER (WHERE status = 'completed') AS net_revenue,
SUM(total) AS gross_revenue
FROM orders
WHERE created_at >= DATE_TRUNC('year', CURRENT_DATE)
GROUP BY 1
ORDER BY 1;FILTER is PostgreSQL syntax. In MySQL the equivalent is SUM(CASE WHEN status = 'completed' THEN total ELSE 0 END).
Making it faster
Grouping by a function of a column means the database cannot use a plain index on created_at for the grouping, although the WHERE clause still benefits from one. If this query runs often over a large table, two options help:
- An index on the expression itself, which PostgreSQL supports directly:
CREATE INDEX ON orders (DATE_TRUNC('month', created_at)); - A stored
monthcolumn, maintained as a generated column, indexed normally.
For most tables neither is necessary. Reach for them when the query is actually slow, not before.
Or just ask for it
Every query above assumes you know your own column names, that total is the amount and that status = 'completed' means paid. On a real schema those assumptions are the hard part, and they are different in every company.
ByeSQL reads your actual schema and writes the query against it, so you can ask for “revenue by month this year, excluding refunds” and get a result with the SQL shown next to it. If the assumption it made about your columns is wrong, you can see it rather than discover it in a meeting.