SQL Data Summarizing with CTEs: A Step-by-Step Guide

SQL Data Summarizing with CTEs: A Step-by-Step Guide

In data analysis, summarizing data over a specific period (e.g., monthly or yearly) is a common requirement. One effective way to achieve this in SQL is by using Common Table Expressions (CTEs). CTEs help organize queries, improve readability, and allow for more manageable and reusable logic within a single query.

Let’s dive into a practical example using a CTE to summarize data from a sales and cost report for recipes.

Scenario: Summarizing Recipe Data by Month and Year

We have a table called RPT_Dyeing_Recipies that contains data about recipes, transactions, their selling prices, and quantities. Our goal is to:

1. Calculate the revenue for each recipe by multiplying the selling price with the lot weight.
2. Sum the cost for each recipe based on quantities and prices.
3. Aggregate the total revenue and total cost by month and year.

Step 1: Define the CTE

In the first part of our query, we define a CTE called RecipeData to aggregate the data at the recipe level for each transaction. We group the data by recipe ID, recipe number, and transaction date, and calculate:

• Revenue: The product of the selling price and the lot weight.
• Cost: The sum of quantities and prices.

Here’s the SQL code to create the CTE:

WITH RecipeData AS (
    SELECT 
        [Recipe_ID],
        [Recipe_NO],
        DATENAME(MONTH, [Transaction_Date]) AS [MonthName],
        YEAR([Transaction_Date]) AS [Year],
        CONCAT(DATENAME(MONTH, [Transaction_Date]), ' ', YEAR([Transaction_Date])) AS [MonthYear],
        MAX([Selling Price] * [Lot_Wt]) AS [Revenue],  -- Summing the revenue
        SUM([Qty_CR] * [Price]) AS [Cost]             -- Summing the cost
    FROM 
        [dbo].[RPT_Dyeing_Recipies]
    GROUP BY 
        YEAR([Transaction_Date]),
        CONCAT(DATENAME(MONTH, [Transaction_Date]), ' ', YEAR([Transaction_Date])),
        [Recipe_ID],
        [Recipe_NO],
        [Transaction_Date]
)

Step 2: Summarizing the Data by Month and Year

Now that we’ve created the CTE, we can proceed to summarize the data by month and year. In this second part of the query, we:

• Aggregate the total revenue and cost for each MonthYear.
• Use the SUM function to calculate the total revenue and total cost by month and year.

Here’s the query that selects the summarized data:

Select the results from the CTE
SELECT 
    [MonthName],
    [Year],
    [MonthYear],
    SUM([Revenue]) AS [TotalRevenue],  -- Aggregating the revenue by month/year
    SUM([Cost]) AS [TotalCost]        -- Aggregating the cost by month/year
FROM 
    RecipeData
GROUP BY
    [MonthName],
    [Year],
    [MonthYear]
ORDER BY 
    [MonthYear] ASC;

Explanation of the SQL Query:

1. CTE Definition (WITH RecipeData AS):
   • We calculate the revenue for each transaction by multiplying the selling price ([Selling Price]) with the lot weight ([Lot_Wt]).
   • The cost is calculated by multiplying the quantity ([Qty_CR]) with the price ([Price]).
   • We group by the year of the transaction (YEAR([Transaction_Date])) and concatenate the month and year into a MonthYear field for easier identification.
2. Final Select:
   • We summarize the total revenue and cost for each month and year combination by grouping the results by MonthName, Year, and MonthYear.
   • The SUM function is used to aggregate the revenue and cost for each month.

Final Output:

This query will return a result like the following:

Benefits of Using CTEs in This Scenario:

• Clarity and Readability: CTEs help break down complex queries into manageable parts. The intermediate results of the CTE (RecipeData) make it easier to understand and maintain the overall query logic.
• Reusability: The CTE can be reused in subsequent parts of the query, which is particularly helpful for complex calculations or aggregations.
• Performance: In many cases, using a CTE can help optimize performance by breaking the query into smaller, more digestible components.

Conclusion:

Using CTEs in SQL for summarizing data is a powerful approach, especially when dealing with complex aggregations and transformations. By structuring your queries with CTEs, you can make them more readable, maintainable, and efficient. This example of summarizing recipe sales and costs by month and year is just one of many potential use cases for CTEs in data analysis.