SQL
Group Records by Month and Year
Aggregate data by month and year from a datetime column in PostgreSQL, providing quick insights into monthly or yearly trends for reports and dashboards.
SELECT
TO_CHAR(order_date, 'YYYY-MM') AS order_month_year,
COUNT(*) AS total_orders,
SUM(total_amount) AS total_revenue
FROM
orders
GROUP BY
TO_CHAR(order_date, 'YYYY-MM')
ORDER BY
order_month_year;
How it works: This query groups order data by month and year to analyze trends over time in PostgreSQL. `TO_CHAR(order_date, 'YYYY-MM')` extracts the year and month from the `order_date` column as a string, which is then used for grouping. The `GROUP BY` clause aggregates `COUNT(*)` for the total number of orders and `SUM(total_amount)` for the total revenue for each unique month-year period, ordered chronologically.