SQL Conditional Expressions and Aggregation

SQL allows you to create flexible queries by combining various conditional expressions and clauses for data retrieval and aggregation.

Conditional Expressions

CASE Expression

The CASE expression provides conditional branching in SQL, similar to if-else if-else or switch statements in programming languages. It can be used in SELECT, WHERE, ORDER BY, and other clauses.

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    -- ... any number of WHEN-THEN clauses
    ELSE resultN -- Default value if no conditions match (optional)
END

Example:

SELECT
    product_name,
    price,
    CASE
        WHEN price >= 1000 THEN 'High Price'
        WHEN price >= 500 THEN 'Mid Price'
        ELSE 'Low Price'
    END AS price_category
FROM
    products;

Aggregation and Grouping

GROUP BY Clause

The GROUP BY clause groups rows based on specified column values and applies aggregate functions (COUNT, SUM, AVG, MAX, MIN, etc.) to each group.

SELECT column_list, aggregate_function(column_name)
FROM table_name
GROUP BY grouping_column;

Example:

SELECT
    category,
    COUNT(*) AS total_products,
    AVG(price) AS average_price
FROM
    products
GROUP BY
    category;

This query groups the products table by the category column and calculates the product count and average price for each category.

HAVING Clause

The HAVING clause applies conditions to filter results after GROUP BY grouping. While the WHERE clause filters individual rows, the HAVING clause filters entire groups.

SELECT column_list, aggregate_function(column_name)
FROM table_name
GROUP BY grouping_column
HAVING group_condition;

Example:

SELECT
    category,
    COUNT(*) AS total_products,
    AVG(price) AS average_price
FROM
    products
GROUP BY
    category
HAVING
    COUNT(*) >= 5; -- Only extract categories with 5 or more products

This query calculates the product count and average price per category, then displays only categories with 5 or more products.

Difference Between WHERE and HAVING

• WHERE clause: Applied before GROUP BY, filtering individual rows. Aggregate functions cannot be used.
• HAVING clause: Applied after GROUP BY, filtering grouped results. Aggregate functions can be used.

By appropriately combining these clauses, you can perform complex data analysis and report generation.