SQL SUM with CASE: Practical Guide

Ryan Forrester

Oct 24th, 2024

Blog

Breakdown of SQL SUM with CASE

SUM with CASE lets you calculate totals based on specific conditions. Instead of filtering data with WHERE clauses and running multiple queries, you can get all your conditional sums in one go. Let’s explore how to use this combination effectively.

Table of Contents

Breakdown of SQL SUM with CASE
Basic Structure and Usage
Real-World Applications
Advanced Techniques
Common Pitfalls and Solutions

Basic Structure and Usage

Here’s the fundamental pattern:

SELECT 
    SUM(CASE 
        WHEN condition THEN value 
        ELSE 0 
    END) AS conditional_sum
FROM table;

A simple example using sales data:

SELECT
    SUM(CASE 
        WHEN payment_type = 'CASH' THEN amount 
        ELSE 0 
    END) AS cash_sales,
    SUM(CASE 
        WHEN payment_type = 'CREDIT' THEN amount 
        ELSE 0 
    END) AS credit_sales
FROM sales;

Real-World Applications

1. Sales Analysis by Time Periods

SELECT 
    category_name,
    SUM(CASE 
        WHEN DATEPART(MONTH, order_date) BETWEEN 1 AND 3 
        THEN sale_amount ELSE 0 
    END) AS q1_sales,
    SUM(CASE 
        WHEN DATEPART(MONTH, order_date) BETWEEN 4 AND 6 
        THEN sale_amount ELSE 0 
    END) AS q2_sales,
    SUM(CASE 
        WHEN DATEPART(MONTH, order_date) BETWEEN 7 AND 9 
        THEN sale_amount ELSE 0 
    END) AS q3_sales,
    SUM(CASE 
        WHEN DATEPART(MONTH, order_date) BETWEEN 10 AND 12 
        THEN sale_amount ELSE 0 
    END) AS q4_sales,
    SUM(sale_amount) AS total_sales
FROM sales s
JOIN categories c ON s.category_id = c.category_id
WHERE YEAR(order_date) = 2024
GROUP BY category_name
ORDER BY total_sales DESC;

2. Customer Segmentation Analysis

SELECT 
    country,
    COUNT(DISTINCT customer_id) AS total_customers,
    SUM(CASE 
        WHEN total_purchases > 1000 THEN 1 
        ELSE 0 
    END) AS premium_customers,
    SUM(CASE 
        WHEN total_purchases BETWEEN 500 AND 1000 THEN 1 
        ELSE 0 
    END) AS regular_customers,
    SUM(CASE 
        WHEN total_purchases < 500 THEN 1 
        ELSE 0 
    END) AS basic_customers,
    FORMAT(AVG(total_purchases), 'C') AS avg_purchase_amount
FROM (
    SELECT 
        customer_id,
        country,
        SUM(purchase_amount) AS total_purchases
    FROM customer_transactions
    GROUP BY customer_id, country
) customer_summary
GROUP BY country
ORDER BY total_customers DESC;

3. Inventory Status Report

SELECT 
    warehouse_name,
    SUM(CASE 
        WHEN current_stock = 0 THEN 1 
        ELSE 0 
    END) AS out_of_stock_items,
    SUM(CASE 
        WHEN current_stock BETWEEN 1 AND reorder_point THEN 1 
        ELSE 0 
    END) AS low_stock_items,
    SUM(CASE 
        WHEN current_stock > reorder_point THEN 1 
        ELSE 0 
    END) AS adequate_stock_items,
    SUM(CASE 
        WHEN current_stock * unit_cost > 10000 THEN 1 
        ELSE 0 
    END) AS high_value_items,
    FORMAT(SUM(current_stock * unit_cost), 'C') AS total_inventory_value
FROM inventory i
JOIN warehouses w ON i.warehouse_id = w.warehouse_id
GROUP BY warehouse_name
ORDER BY total_inventory_value DESC;

4. Employee Performance Metrics

SELECT 
    d.department_name,
    COUNT(e.employee_id) AS total_employees,
    FORMAT(AVG(e.salary), 'C') AS avg_salary,
    SUM(CASE 
        WHEN performance_score >= 90 THEN 1 
        ELSE 0 
    END) AS outstanding_performers,
    SUM(CASE 
        WHEN performance_score BETWEEN 75 AND 89 THEN 1 
        ELSE 0 
    END) AS above_average_performers,
    SUM(CASE 
        WHEN performance_score BETWEEN 60 AND 74 THEN 1 
        ELSE 0 
    END) AS average_performers,
    SUM(CASE 
        WHEN performance_score < 60 THEN 1 
        ELSE 0 
    END) AS below_average_performers,
    FORMAT(SUM(CASE 
        WHEN performance_score >= 90 THEN bonus_amount 
        ELSE 0 
    END), 'C') AS total_bonus_paid
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN performance_reviews pr ON e.employee_id = pr.employee_id
WHERE YEAR(review_date) = 2024
GROUP BY d.department_name
ORDER BY total_bonus_paid DESC;

Build a Web Application using only SQL

Building an entire web app using only SQL is not only possible but can be incredibly efficient with the right tools. With Five, a rapid application development environment, you can create a fully responsive SQL app that runs on top of a MySQL database without needing any additional programming languages or frameworks.

The step-by-step tutorial available here will guide you through creating:

A MySQL-based database for your web app.
A form-based user interface that dynamically interacts with the data.
Interactive charts and comprehensive PDF reports directly from SQL queries.
A secure login system, turning your SQL queries into a multiuser app.

Using Five, the development process is simplified. The platform automatically generates forms, charts, reports, and other front-end elements based on your SQL schema, meaning you can build an entire application with just SQL and Five’s intuitive visual tools.

By the end the tutorial available here “How To Build a SQL App“, you will have a fully functional web app that uses SQL to manage data, generate reports, and provide a dynamic user experience — all without writing any additional code outside SQL.

Get started with free access to Five’s development environment and begin building your SQL-powered web app today!

Build Your SQL Web App In 3 Steps
Start Developing Today

Advanced Techniques

1. Multiple Conditions in CASE

SELECT 
    product_category,
    SUM(CASE 
        WHEN sales_amount > 1000 AND customer_type = 'Premium' 
        THEN sales_amount * 0.15
        WHEN sales_amount > 500 AND customer_type = 'Regular'
        THEN sales_amount * 0.10
        ELSE sales_amount * 0.05
    END) AS commission_earned,
    COUNT(DISTINCT 
        CASE 
            WHEN sales_amount > 1000 
            THEN customer_id 
        END
    ) AS high_value_customers
FROM sales_data
GROUP BY product_category;

2. Date-Based Calculations

SELECT 
    product_name,
    SUM(CASE 
        WHEN DATEDIFF(DAY, order_date, GETDATE()) <= 30 
        THEN quantity 
        ELSE 0 
    END) AS last_30_days_sales,
    SUM(CASE 
        WHEN DATEDIFF(DAY, order_date, GETDATE()) BETWEEN 31 AND 60 
        THEN quantity 
        ELSE 0 
    END) AS previous_30_days_sales,
    SUM(CASE 
        WHEN DATEDIFF(DAY, order_date, GETDATE()) BETWEEN 61 AND 90 
        THEN quantity 
        ELSE 0 
    END) AS third_30_days_sales,
    SUM(quantity) AS total_quantity
FROM orders o
JOIN products p ON o.product_id = p.product_id
WHERE order_date >= DATEADD(DAY, -90, GETDATE())
GROUP BY product_name;

3. Nested CASE Statements

SELECT 
    region,
    SUM(CASE 
        WHEN order_status = 'Completed' THEN
            CASE
                WHEN order_value > 1000 THEN order_value * 0.20
                WHEN order_value > 500 THEN order_value * 0.15
                ELSE order_value * 0.10
            END
        ELSE 0 
    END) AS total_commission,
    COUNT(DISTINCT 
        CASE 
            WHEN order_status = 'Completed' AND 
                 order_value > 1000 
            THEN sales_rep_id 
        END
    ) AS high_performing_reps
FROM sales_orders
GROUP BY region;

Common Pitfalls and Solutions

1. Handling NULL Values

-- Problematic query
SELECT 
    SUM(CASE 
        WHEN amount > 1000 THEN amount 
    END) AS high_value_sales  -- NULL if no matches

-- Fixed version
SELECT 
    SUM(CASE 
        WHEN amount > 1000 THEN amount 
        ELSE 0  -- Explicitly handle non-matching cases
    END) AS high_value_sales
FROM sales;

2. Incorrect Grouping

-- Problematic query - missing GROUP BY
SELECT 
    category,
    SUM(CASE 
        WHEN status = 'Active' THEN 1 
        ELSE 0 
    END) AS active_count
FROM items;

-- Fixed version
SELECT 
    category,
    SUM(CASE 
        WHEN status = 'Active' THEN 1 
        ELSE 0 
    END) AS active_count
FROM items
GROUP BY category;

3. Performance Optimization

-- Instead of multiple separate queries
SELECT COUNT(*) FROM orders WHERE status = 'Pending';
SELECT COUNT(*) FROM orders WHERE status = 'Completed';
SELECT COUNT(*) FROM orders WHERE status = 'Cancelled';

-- Use a single query with conditional counting
SELECT 
    SUM(CASE WHEN status = 'Pending' THEN 1 ELSE 0 END) AS pending_orders,
    SUM(CASE WHEN status = 'Completed' THEN 1 ELSE 0 END) AS completed_orders,
    SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled_orders
FROM orders;

Using SUM with CASE statements lets you create flexible, efficient queries that handle complex business logic in a single pass through the data. Remember to handle NULL values explicitly and always verify your grouping logic to ensure accurate results.