How to use CASE Statement In SQL
One common approach to the conditional summation in MySQL involves using the CASE statement within the SUM function. The CASE statement allows us to evaluate conditions for each row and return different values based on those conditions.
Syntax:
SELECT SUM(CASE
WHEN condition1 THEN value1
WHEN condition2 THEN value2
ELSE default_value
END) AS sum_column
FROM table_name;
Example
Suppose we have a table named sales with the columns product_id and quantity_sold. We want to the calculate the total quantity sold for the specific product.
we can insert sample data into the ‘sales‘ table to the test the query:
INSERT INTO sales (product_id, quantity_sold) VALUES (1, 10), (2, 20), (1, 5);
After inserting the data we can run the original query again to the calculate the total quantity sold for the product_id = 1:
SELECT SUM(CASE
WHEN product_id = 1 THEN quantity_sold
ELSE 0
END) AS total_quantity_sold
FROM sales;
This should return the correct total quantity sold for the product_id = 1 based on the sample data you inserted.
Explanation: In this example, the CASE statement evaluates each row’s product_id. If the product_id equals 1 and it adds the quantity_sold to the sum. Otherwise, it adds 0 to sum effectively excluding the rows with the different product_id.
Conditional Summation
Users can determine the total of the values in a column depending on certain conditions by using MySQL’s Conditional Summation feature. By enabling users to do calculations on data subsets that satisfy particular needs this feature improves data analysis and manipulating capabilities. One’s capacity for data analysis and decision-making may be greatly improved by knowing how to use conditional summation.
Contact Us