SQL
Aggregating Sales Data by Month and Year
Learn to aggregate and analyze data by specific time periods like month and year, and filter results within a date range, essential for reporting and analytics.
SELECT
EXTRACT(YEAR FROM order_date) AS sales_year,
EXTRACT(MONTH FROM order_date) AS sales_month,
SUM(total_amount) AS monthly_sales_total,
COUNT(id) AS number_of_orders
FROM
orders
WHERE
order_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY
sales_year, sales_month
ORDER BY
sales_year, sales_month;
How it works: This query demonstrates how to group and aggregate data based on specific date parts, such as year and month. Using EXTRACT(PART FROM date_column) (PostgreSQL/Oracle) or YEAR() and MONTH() (MySQL) allows you to break down data into temporal segments for reporting. The WHERE clause filters the results to a specific date range, providing focused analytics.