Performing conditional aggregation using the SUM() function in SQL can be accomplished by utilizing CASE statements to specify the conditions under which the data should be summed. The CASE statement allows you to create a condition that determines whether each row should be included in the aggregation.
Here's the basic syntax for using SUM() with a CASE statement:
SELECT
column_name,
SUM(CASE
WHEN condition THEN value
ELSE 0
END) AS alias_name
FROM
table_name
GROUP BY
column_name;
Example
Let's say you have a table named sales with the following columns:
- salesperson (the name of the salesperson)
- amount (the sales amount)
- region (the sales region)
If you want to calculate the total sales per salesperson, but only for those sales that were in the East region, you could do it as follows:
SELECT
salesperson,
SUM(CASE
WHEN region = 'East' THEN amount
ELSE 0
END) AS total_sales_east
FROM
sales
GROUP BY
salesperson;
Explanation:
1. SELECT Statement: Specifies the columns to be retrieved and the calculated column.
2. CASE Statement: Evaluates each row in the sales table:
- If the region is 'East', it takes the amount value.
- If the region is not 'East', it returns 0 (effectively ignoring these rows for the sum).
3. SUM() Function: Sums all the values returned by the CASE statement for each salesperson.
4. GROUP BY Clause: Groups the results by salesperson, so you get one total per salesperson.
You can extend this logic further by adding additional SUM statements for other conditions or by combining different aggregations in a single query. For example, you could calculate sales totals for other regions by using additional SUM() functions with respective conditions in the same SELECT query.