PostgreSQL is a powerful tool that includes advanced features in the field of data analysis and database management. One of its significant useful functions is the capacity to carry out conditional summation, which allows users to add up numbers based on given conditions.
This article goes through the concept of conditional summation in PostgreSQL, illustrating its usage and providing practical examples.
What is Conditional Summation
Conditional summation is a type of calculation that involves the summation of data values from a particular dataset using certain conditions or criteria as a basis. SUM() function along with the CASE statement enables us to accomplish the task for PostgreSQL. The CASE statement is the conditional one, which sets up the conditions and returns the result of the first condition that is true.
Syntax:
The syntax for conditional summation in PostgreSQL can be summarized as follows:
SELECT SUM(CASE
WHEN condition1 THEN value1
WHEN condition2 THEN value2
...
ELSE default_value
END) AS sum_result
FROM table_name;
In this syntax:
- condition1, condition2, etc., represent the conditions to be evaluated.
- value1, value2, etc., represent the values to be summed if the corresponding condition is true.
- default_value is the value returned if none of the conditions are met.
- table_name is the name of the table from which data is being retrieved.
Now let’s start by creating a table and inserting some sample data into it. We’ll then proceed to demonstrate some example queries for conditional summation in PostgreSQL.
Create a table named sales and insert some data into it.
CREATE TABLE sales (
product_id SERIAL PRIMARY KEY,
quantity_sold INTEGER,
unit_price NUMERIC,
category VARCHAR(50)
);
INSERT INTO sales (quantity_sold, unit_price, category) VALUES
(10, 15.50, 'Electronics'),
(5, 20.25, 'Electronics'),
(8, 10.75, 'Clothing'),
(15, 7.99, 'Clothing'),
(20, 5.50, 'Home Appliances'),
(10, 12.75, 'Home Appliances');
Output:
You can see the table content below:
Sales Table
Example Queries of Conditional Summation
We have our table set up with some sample data, let’s proceed to demonstrate some example queries for conditional summation.
Example 1: Total sales amount for each category, considering only positive quantities sold.
Query:
SELECT category,
SUM(CASE
WHEN quantity_sold > 0 THEN quantity_sold * unit_price
ELSE 0
END) AS total_sales
FROM sales
GROUP BY category;
Explanation:
- It groups the data by the category column.
- For each group (category), it calculates the total sales by summing up the product of quantity_sold and unit_price for each sale, but only if quantity_sold is greater than 0. Otherwise, it adds 0 to the sum.
- It gives the calculated sum an alias total_sales.
Output:
This query will be a table with two columns: category and total_sales. Each row in the output represents a category along with the total sales for that category.
Total sales amount for each category
Example 2: Total sales amount for each category, considering only sales with a unit price greater than $10:
Query:
SELECT category,
SUM(CASE
WHEN unit_price > 10 THEN quantity_sold * unit_price
ELSE 0
END) AS total_sales
FROM sales
GROUP BY category;
Explanation:
- It groups the data by the category column.
- For each group (category), it calculates the total sales by summing up the product of quantity_sold and unit_price for each sale, but only if the unit_price is greater than 10. Otherwise, it adds 0 to the sum.
- It gives the calculated sum an alias total_sales.
Output:
Each row in the output represents a category along with the total sales for that category, considering only sales where the unit price is greater than 10.
Total sales amount where sales with a unit price greater than 10
Example 3: Total sales amount for each category, considering only sales of electronics.
Query:
SELECT category,
SUM(CASE
WHEN category = 'Electronics' THEN quantity_sold * unit_price
ELSE 0
END) AS total_sales
FROM sales
GROUP BY category;
Explanation:
- It groups the sales data by the category column.
- For each group (category), it calculates the total sales by summing up the product of quantity_sold and unit_price for each sale within the ‘Electronics’ category. For all other categories, it adds 0 to the sum.
- It gives the calculated sum an alias total_sales.
Output:
Each row in the output represents a category, and for the ‘Electronics‘ category, it displays the total sales amount, while for other categories, it displays 0 as the total sales.
Total sales amount considering only sales of electronics
Example 4: Total sales amount for each category, considering only sales where the quantity sold is greater than 5.
Query:
SELECT category,
SUM(CASE
WHEN quantity_sold > 5 THEN quantity_sold * unit_price
ELSE 0
END) AS total_sales
FROM sales
GROUP BY category;
Explanation:
- It groups the data by the category column.
- For each group (category), it calculates the total sales by summing up the product of quantity_sold and unit_price for each sale where quantity_sold is greater than 5. If quantity_sold is not greater than 5, it adds 0 to the sum.
- It assigns the calculated sum an alias total_sales.
Output:
Each row in the output represents a category along with the total sales for that category, considering only sales where the quantity sold is greater than 5.
Total sales amount only sales where the quantity sold is greater than 5
Conclusion
A conditional summation in PostgreSQL allows a way to do data aggregation by calculating the total data based on declared conditions. The combination of SUM() function and CASE statement gives users the flexibility of customizing their summation requests by considering specific requirements. Regardless of whether it is used for analyzing sales data, financial transactions or any other dataset, conditional summation remains as a very useful and essential tool in the PostgreSQL toolbox at all times.