GROUB BY Example: Monthly Sales Summary by Department

Scenario

Suppose you have a Sales table that logs every sale made in a retail store, including the transaction date, the department from which the item was sold, and the revenue generated from each sale. We want to create a report that summarizes the total revenue and the number of sales per department for each month.

Database Schema

Here’s a simple schema for the Sales table:

• Sales
  • SaleID (int)
  • Department (varchar)
  • SaleDate (date)
  • Revenue (decimal)

SQL Query

The query will group sales by Department and the month of SaleDate, and calculate the total revenue and count of sales per department per month:

SELECT
    Department,
    EXTRACT(YEAR FROM SaleDate) AS SaleYear,
    EXTRACT(MONTH FROM SaleDate) AS SaleMonth,
    COUNT(*) AS TotalSales,
    SUM(Revenue) AS TotalRevenue
FROM
    Sales
GROUP BY
    Department,
    EXTRACT(YEAR FROM SaleDate),
    EXTRACT(MONTH FROM SaleDate)
ORDER BY
    Department,
    SaleYear,
    SaleMonth;

Explanation of the Code

1. Column Selection:
   • Department: Directly selects the department from which the sale was made.
   • EXTRACT(YEAR FROM SaleDate) AS SaleYear and EXTRACT(MONTH FROM SaleDate) AS SaleMonth: Extracts the year and month from the SaleDate to facilitate grouping by these intervals.
   • COUNT(*) AS TotalSales: Counts the total number of sales transactions per group.
   • SUM(Revenue) AS TotalRevenue: Sums up the revenue generated by the sales in each group.
2. GROUP BY Clause:
   • Groups the data by Department, SaleYear, and SaleMonth. This arrangement ensures that the data is summarized per month for each department, providing a detailed view of sales activity over time.
3. ORDER BY Clause:
   • Orders the results by Department, and within each department, it sorts by SaleYear and SaleMonth. This ordering is crucial for presenting the data in a logical, chronological sequence.

Example Output

Assuming the sales data includes multiple entries across various departments, the output might look something like this: