Example of Conditional Summation in SQL

Example 1: Calculate the total sales amount for each product that is ordered with a quantity greater than 5.

Step 1: Sample Data

Let us consider the orders table which consists of order_id, product_id, quantity, unit_price columns in it.

Step 2: Implement the Query

We can write the below code to calculate the total sales amount for each product which is orders with a quantity greater than 5.

SELECT 
    product_id,
    SUM(CASE WHEN quantity > 5 THEN quantity * unit_price ELSE 0 END) AS total_sales
FROM 
    orders
GROUP BY 
    product_id;

Output:

The below output shows the item details, which items quantity have greater than 5.

Explanation of code:

1. For the product_id = 101, there are two rows in the orders table, but only one row has a quantity greater than 5. So, that the total sales for the product_id = 101 will be 6 * 10.00 = 60.00.
2. For the product_id = 102, there are two rows with a quantity greater than 5. So, the total sales for the product_id = 102 will be (7 * 15.00) + ( 8 * 15.00) = 210.00.
3. For the product_id = 103 is only one row, but the quantity is less than 5. So, it cannot calculate the sum.

Example 2: Calculate the total amount spent in each category, but only for the transactions that occurred after a specific date.

Step 1: Sample Data

Let us consider the transactions table with the columns transactions_id, category, amount, and transaction_date.

Step 2: Implement the Query:

Write the below code to calculate the total amount spent in each category, but only for the transactions that occurred after a specific date.

SELECT 
    category,
    SUM(CASE WHEN transaction_date > '2024-04-05' THEN amount ELSE 0 END) AS total_spent
FROM 
    transactions
GROUP BY 
    category;

Output:

The below output shows which items were bought after 2024-04-05.

Explanation of code:

1. For the Clothing category, there is only one transaction, but it occurred before 2024-04-05. So, it cannot sum the amount.
2. For the Electronics category, there is only one transaction and it occurred after 2024-04-05. So the total amount spent in the Electronics category after 2024-04-05 will be 120.00.
3. For the Food category, there are 3 transactions, but only one transaction occurred after 2024-04-05. So, the total amount spent in the Food category after 2024-04-05 will be 40.00.

In data manipulation, SQL (Structured Query Language) stands as a powerful tool for extracting, transforming, and analyzing data stored in relational databases. Among its numerous functionalities, SQL provides robust capabilities for summation operations, allowing users to aggregate data according to specific conditions.