PostgreSQL SUM
Calculate total values in your database
➕ What is PostgreSQL SUM?
The SUM function calculates the total of numeric values in a column. It adds all numbers together and returns a single result, making it perfect for calculating totals like sales, expenses, or quantities.
-- Calculate total sales
SELECT SUM(amount) AS total_sales
FROM orders;
Basic SUM Syntax
Simple SUM
Add all values in a column
SELECT SUM(price) FROM products;SUM with WHERE
Calculate sum with conditions
SELECT SUM(salary)
FROM employees
WHERE department = 'Sales';SUM with GROUP BY
Sum values by category
SELECT category, SUM(quantity)
FROM inventory
GROUP BY category;SUM with DISTINCT
Sum only unique values
SELECT SUM(DISTINCT price)
FROM products;🔹 Basic SUM Example
The SUM function adds all numeric values in a specified column. It ignores NULL values automatically and returns the total sum. This is useful for calculating totals like revenue, expenses, or inventory counts across your entire dataset.
-- Sample data: orders table
-- order_id | product_name | amount
-- 1 | Laptop | 1200
-- 2 | Mouse | 25
-- 3 | Keyboard | 75
-- 4 | Monitor | 300
SELECT SUM(amount) AS total_revenue
FROM orders;Output:
| total_revenue |
|---|
| 1600 |
🔹 SUM with WHERE Clause
Combine SUM with WHERE to calculate totals based on specific conditions. This allows you to filter data before summing, such as calculating sales for a particular product, region, or time period. The WHERE clause filters rows first, then SUM calculates the total.
-- Calculate total sales for laptops only
SELECT SUM(amount) AS laptop_sales
FROM orders
WHERE product_name = 'Laptop';Output:
| laptop_sales |
|---|
| 1200 |
🔹 SUM with GROUP BY
Use GROUP BY with SUM to calculate subtotals for different categories or groups. This creates separate sum calculations for each unique value in the grouped column. It's perfect for generating reports showing totals by department, product category, or customer segment.
-- Sample data: sales table
-- region | amount
-- North | 5000
-- South | 3000
-- North | 2000
-- East | 4000
SELECT region, SUM(amount) AS regional_total
FROM sales
GROUP BY region
ORDER BY regional_total DESC;Output:
| region | regional_total |
|---|---|
| North | 7000 |
| East | 4000 |
| South | 3000 |
🔹 SUM with Multiple Columns
You can calculate sums for multiple columns in a single query by using SUM multiple times. Each SUM function operates independently on its specified column. This is useful for comparing different metrics side by side, like total revenue versus total costs in financial reports.
-- Calculate multiple totals at once
SELECT
SUM(revenue) AS total_revenue,
SUM(cost) AS total_cost,
SUM(revenue - cost) AS total_profit
FROM transactions;Output:
| total_revenue | total_cost | total_profit |
|---|---|---|
| 50000 | 30000 | 20000 |
🔹 SUM with HAVING Clause
The HAVING clause filters groups after SUM calculations are performed, unlike WHERE which filters before. Use HAVING when you need to filter based on aggregate results, such as finding departments with total sales above a certain threshold. It works only with GROUP BY statements.
-- Find departments with total salary over 100000
SELECT department, SUM(salary) AS dept_total
FROM employees
GROUP BY department
HAVING SUM(salary) > 100000;Output:
| department | dept_total |
|---|---|
| Engineering | 250000 |
| Sales | 180000 |
💡 Important Notes:
- SUM only works with numeric data types (INTEGER, DECIMAL, FLOAT)
- NULL values are automatically ignored in calculations
- If all values are NULL, SUM returns NULL
- Use COALESCE to convert NULL results to 0: COALESCE(SUM(amount), 0)
- SUM can cause overflow with very large numbers