In this tutorial, we will learn how to use the CASE statement with the SUM function and a GROUP BY clause.
This tutorial does not cover the fundamentals of working with either SQL Case statement, the sum() function, or the GROUP BY clause. If you are looking for the basics of these SQL features, check our tutorials on the topics to discover further.
Problem:
Let’s say we have a table named “orders” and we wish to get the total sales for each customer who are grouped by customer ID, but we also want to calculate a discount for customers who placed more than two orders.
The table is shown in the following:
order_id INT PRIMARY KEY,
customer_id INT,
product_id INT,
order_date DATE,
quantity INT,
price DECIMAL(10,2)
);
Insert a sample data into the table as shown in the following:
VALUES
(1, 101, 1, '2022-04-01', 2, 10.99),
(2, 102, 2, '2022-04-01', 1, 19.99),
(3, 103, 1, '2022-04-02', 3, 8.99),
(4, 101, 3, '2022-04-03', 2, 15.99),
(5, 102, 1, '2022-04-03', 1, 12.99),
(6, 104, 2, '2022-04-04', 4, 7.99),
(7, 103, 3, '2022-04-05', 2, 21.99),
(8, 101, 2, '2022-04-06', 1, 18.99),
(9, 104, 1, '2022-04-07', 2, 9.99),
(10, 102, 3, '2022-04-07', 3, 14.99);
This should create a table as follows:
SQL Case with the Sum and Group By Clause
To fetch the total sales for each customer who are grouped by ID and calculate the discount for customers who placed more than two orders, we can use the CASE statement in conjunction with the SUM and GROUP BY clause as shown in the following:
In the given example, we use the SQL CASE statement to check if the customer has placed more than two orders.
If a customer has placed more than two orders, we multiply the total_sales by 0.9 which applies a 10% discount.
Conclusion
We discussed how we can use the SQL CASE statement with the SUM() and GROUP BY clause.
