PostgreSQL does not provide a built-in SUMIF function like some other databases do. However, you can achieve similar functionality by combining the SUM and CASE statements in PostgreSQL.
The SUM function in PostgreSQL calculates the sum of a given column or expression. The CASE statement allows you to perform conditional logic.
Let’s say you have a table named “sales” with the following data:
| id | product | quantity | price |
|---|---|---|---|
| 1 | Apple | 5 | 0.50 |
| 2 | Orange | 3 | 0.30 |
| 3 | Apple | 2 | 0.50 |
| 4 | Banana | 4 | 0.40 |
If you want to calculate the total sales for the product “Apple,” you can use the following query:
SELECT SUM(CASE WHEN product = 'Apple' THEN quantity * price ELSE 0 END) AS total_sales
FROM sales;
This query uses the CASE statement to check if the product is “Apple.” If it is, it multiplies the quantity by the price; otherwise, it returns 0. The SUM function then calculates the sum of these values and aliases the result as “total_sales.”
In this case, the result would be:
total_sales
-----------
3.50
The query multiplies the quantity (5) by the price (0.50) for the first row where the product is “Apple,” and it multiplies the quantity (2) by the price (0.50) for the third row. The sum of these values is 3.50.
You can modify the query to calculate the total sales for different products or to add more conditions using the CASE statement.