Query Explanation - 2: Calculating Total Sales for Each Month of the Current Year (Including Zero Sales)

In this blog post, we will walk through the process of calculating the total sales for each month of the current year, ensuring that months with zero sales are included in the result. This is a common reporting requirement when analyzing monthly sales data, as businesses often want to see trends over time, including months without any sales.

Problem Statement:

We need to calculate the total sales for each month of the current year and display the result in such a way that even months with no sales are shown with a total of zero.

Example Schema:

Assume we have the following two tables:

1. Sales:

   • SaleID (Primary Key)
   • SaleDate (DateTime)
   • Amount (Decimal)

2. Calendar (optional or created on the fly):

   • MonthNumber (Integer, 1 for January, 2 for February, etc.)
   • MonthName (String, 'January', 'February', etc.)

Never miss new posts by subscribe

Powered by

SQL Query:

To achieve this, we need to perform the following steps:

1. Generate a list of all months in the current year.
2. Aggregate the sales data to calculate the total sales per month.
3. Perform a left join between the generated months and the sales data to include months with zero sales.

We can generate the list of months dynamically using SQL's DATE functions and perform the aggregation using SUM() with a GROUP BY clause. Here's how the query can be constructed:

WITH Months AS (
    SELECT 
        1 AS MonthNumber, 'January' AS MonthName
    UNION ALL
    SELECT 
        2, 'February'
    UNION ALL
    SELECT 
        3, 'March'
    UNION ALL
    SELECT 
        4, 'April'
    UNION ALL
    SELECT 
        5, 'May'
    UNION ALL
    SELECT 
        6, 'June'
    UNION ALL
    SELECT 
        7, 'July'
    UNION ALL
    SELECT 
        8, 'August'
    UNION ALL
    SELECT 
        9, 'September'
    UNION ALL
    SELECT 
        10, 'October'
    UNION ALL
    SELECT 
        11, 'November'
    UNION ALL
    SELECT 
        12, 'December'
)
SELECT 
    m.MonthName,
    ISNULL(SUM(s.Amount), 0) AS TotalSales
FROM 
    Months m
LEFT JOIN 
    Sales s ON MONTH(s.SaleDate) = m.MonthNumber AND YEAR(s.SaleDate) = YEAR(GETDATE())
GROUP BY 
    m.MonthName, m.MonthNumber
ORDER BY 
    m.MonthNumber;

Detailed Breakdown:

1. Common Table Expression (CTE) to Generate Months:

   • The WITH Months AS (...) block generates a list of months (both names and numbers) from January to December.
   • The UNION ALL is used to manually define each month in the year.
   • We could also generate months dynamically using a date sequence function, but for simplicity, this example assumes manually generated months.

2. LEFT JOIN with Sales Table:

   • The LEFT JOIN between the Months CTE and the Sales table ensures that every month from the Months list is included in the result, even if there are no matching sales for that month.
   • MONTH(s.SaleDate) = m.MonthNumber matches the sales data to the corresponding month.
   • YEAR(s.SaleDate) = YEAR(GETDATE()) restricts the sales data to only the current year. The GETDATE() function returns the current date, and YEAR(GETDATE()) extracts the current year from it.

3. Aggregating Sales Data:

   • The SUM(s.Amount) function is used to calculate the total sales for each month.
   • ISNULL(SUM(s.Amount), 0) ensures that if a month has no sales, it will return 0 instead of NULL.

4. Grouping and Ordering Results:

   • The GROUP BY clause is used to group the results by month, ensuring we get the total sales per month.
   • The results are ordered by MonthNumber to ensure the months appear in chronological order.

Example Output:

Let's say we have the following data in the Sales table for the current year:

After executing the query, the result would look like this:

Never miss new posts by subscribe

Powered by

Key Takeaways:

• Handling Months with Zero Sales: The use of a LEFT JOIN and a manually generated months list ensures that months with no sales are included in the result with a value of zero.
• Aggregating by Time Period: The SUM() function is used to calculate total sales, but it can also be extended to other aggregates such as COUNT(), AVG(), etc., based on your reporting needs.
• Dynamically Working with Dates: Using YEAR(GETDATE()) allows the query to adapt to the current year, meaning it will always calculate sales for the ongoing year without hardcoding the year.

By using these techniques, you can ensure that your monthly sales reports are complete and show a clear picture, even when certain months have no sales activity.