Advanced SQL: Conditional Aggregation

Conditional aggregation is a powerful technique in SQL that allows you to perform aggregate functions based on specified conditions. It enables you to calculate aggregate values selectively, depending on the data characteristics or specific criteria. In this article, we’ll delve into the concept of conditional aggregation and illustrate its application with various code examples.

Photo from Pexels

Understanding Conditional Aggregation

Traditional aggregation functions like COUNT(), SUM(), AVG(), and others provide valuable insights by summarizing data across rows. However, they consider all rows equally, which may not always be suitable for nuanced analysis. Conditional aggregation addresses this limitation by allowing you to apply aggregate functions selectively based on specified conditions.

Example Scenario: Analyzing Sales Data

Suppose we have a table named “sales” containing information about sales transactions, including the store ID (“stor_id”), the quantity sold (“qty”), and the order date (“ord_date”). We want to analyze the total sales for each store in the year 1993.

Applying Conditional Aggregation

Let’s explore how we can use conditional aggregation to achieve our analysis objective.

Example 1: Total Sales in 1993

SELECT stor_id,
    SUM(CASE WHEN YEAR(ord_date) = 1993 THEN qty ELSE 0 END) AS total_sales
FROM sales
GROUP BY stor_id
ORDER BY total_sales DESC;

In this SQL query:

• We select the “stor_id” column from the “sales” table.
• We apply a conditional SUM() function to calculate the total quantity sold for each store in the year 1993. The CASE statement evaluates whether the year of each order date is 1993. If true, it adds the quantity to the sum; otherwise, it adds 0.
• Finally, we group the results by the “stor_id” column using the GROUP BY clause.

Example 2: Average Sales per Month

SELECT stor_id, MONTH(ord_date) AS month,
       AVG(CASE WHEN YEAR(ord_date) = 1993 THEN qty ELSE 0 END) AS avg_sales
FROM sales
WHERE YEAR(ord_date) = 1993
GROUP BY stor_id, month
ORDER BY stor_id;

In this SQL query:

• We calculate the average quantity sold per month for each store in the year 1993.
• We apply a conditional AVG() function to calculate the average quantity sold, considering only the orders placed in 1993. The CASE statement evaluates whether the year of each order date is 1993. If true, it includes the quantity in the average calculation; otherwise, it includes 0.
• We include the “stor_id” and month extracted from the order date for more granular analysis.
• We filter the results to include only orders placed in 1993 using the WHERE clause.

Example 3: New Sales Category Column

SELECT stor_id,
       SUM(CASE WHEN YEAR(ord_date) = 1993 THEN qty ELSE 0 END) AS total_sales_1993,
       CASE
           WHEN SUM(CASE WHEN YEAR(ord_date) = 1993 THEN qty ELSE 0 END) < 1000 THEN 'Low Sales'
           WHEN SUM(CASE WHEN YEAR(ord_date) = 1993 THEN qty ELSE 0 END) BETWEEN 1000 AND 5000 THEN 'Medium Sales'
           ELSE 'High Sales'
       END AS sales_category
FROM sales
GROUP BY stor_id;

In this SQL query:

• We calculate the total sales for each store in the year 1993 using the SUM() function, similar to the previous example.
• We then use a CASE WHEN statement to categorize the total sales into different categories based on the specified conditions.
• If the total sales are less than 1000, the category is set to ‘Low Sales’.
• If the total sales are between 1000 and 5000 (inclusive), the category is set to ‘Medium Sales’.
• Otherwise, if the total sales exceed 5000, the category is set to ‘High Sales’.

Conclusion

Conditional aggregation offers a flexible and insightful approach to data analysis in SQL, allowing you to derive meaningful insights from your datasets. By selectively applying aggregate functions based on specified conditions, you can gain deeper understanding and make more informed decisions.

In this article, we’ve explored the concept of conditional aggregation through practical examples involving sales data. Incorporating conditional aggregation techniques into your SQL queries can enhance your analytical capabilities and empower you to extract valuable insights from your data.

SQL Fundamentals

Thank you for your time and interest! 🚀 You can find even more content at **SQL Fundamentals 💫**