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.

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.

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.

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.

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.