Time is Money: Grouping by Date

The Trend Request

Every business needs to see trends. *"Show me revenue by month for the past year,"* the CEO asked. *"I want to see the growth trajectory."*

Grouping by 'Country' is easy—it's already a clean category. But 'Date' is messy. Orders come in on January 15th, 16th, 17th... Grouping by the raw date would give me 365 rows.

The Quest: Date Truncation

To create a "Monthly" view, we need to tell SQL to ignore the day and treat all dates in January as just "January." We use the `DATE_TRUNC` function (PostgreSQL) or similar functions in other databases.

The Implementation: The Time Rollup

PostgreSQL / SQL Style

-- Revenue by month

SELECT

DATE_TRUNC('month', order_date) AS order_month,

SUM(order_amount) AS monthly_revenue

FROM orders

GROUP BY DATE_TRUNC('month', order_date)

ORDER BY order_month;

MySQL Style (Using YEAR/MONTH functions)

SELECT

YEAR(order_date) AS order_year,

MONTH(order_date) AS order_month,

SUM(order_amount) AS monthly_revenue

FROM orders

GROUP BY YEAR(order_date), MONTH(order_date)

ORDER BY order_year, order_month;

The "Oops" Moment

I once forgot to `ORDER BY` a time-based grouping. The months came out in a random order: March, January, November.

**Pro Tip**: Time-series data MUST be sorted by the time column. Otherwise, the "Trend Line" is broken. Always add `ORDER BY` at the end.

The Victory

The CEO got a clean 12-row table showing revenue climbing month over month. This is the kind of chart شما paste directly into a pitch deck for investors. Time-based aggregation is the foundation of growth analysis.

Your Task for Today

Group your orders (or any table with dates) by Month. Calculate the count per month and see which was your busiest.

*Day 21: Business KPIs in SQL—Real Metrics.*