SQL — Sum

Basic Syntax

SELECT SUM(column_name) AS total
FROM table_name;

Simple Example

SELECT SUM(amount) AS total_revenue
FROM orders;

SUM with WHERE

-- Total revenue from completed orders
SELECT SUM(amount) AS total_revenue
FROM orders
WHERE status = 'completed';

SUM with GROUP BY

SELECT
  department,
  SUM(salary) AS total_payroll
FROM employees
GROUP BY department
ORDER BY total_payroll DESC;

SUM with JOIN

SELECT
  c.category_name,
  SUM(oi.quantity * oi.unit_price) AS total_sales
FROM order_items oi
JOIN products p ON oi.product_id = p.id
JOIN categories c ON p.category_id = c.id
GROUP BY c.category_name;

SUM with ROUND

SELECT
  department,
  ROUND(SUM(salary), 2) AS total_payroll
FROM employees
GROUP BY department;

SUM Multiple Columns

SELECT
  order_id,
  SUM(quantity) AS total_items,
  SUM(quantity * unit_price) AS total_value
FROM order_items
GROUP BY order_id;

SUM with HAVING

SELECT
  customer_id,
  SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 1000;

SUM Ignores NULLs

CREATE TABLE sales (amount INT);
INSERT INTO sales VALUES (100), (NULL), (200);

SELECT SUM(amount) FROM sales;  -- 300 (NULL ignored)

Running Total

SELECT
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

SUM with CASE

Conditional summing:

SELECT
  SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS completed_total,
  SUM(CASE WHEN status = 'pending' THEN amount ELSE 0 END) AS pending_total,
  SUM(CASE WHEN status = 'cancelled' THEN amount ELSE 0 END) AS cancelled_total
FROM orders;

NULLIF to Avoid Division by Zero

SELECT
  product_name,
  SUM(revenue) / NULLIF(SUM(quantity), 0) AS avg_price_per_unit
FROM sales
GROUP BY product_name;

Practical Example

-- Monthly revenue summary
SELECT
  DATE_FORMAT(order_date, '%Y-%m') AS month,
  COUNT(*) AS order_count,
  SUM(amount) AS total_revenue,
  ROUND(AVG(amount), 2) AS avg_order_value
FROM orders
WHERE status = 'completed'
GROUP BY month
ORDER BY month DESC;

Practice

1. Calculate total sales for each product
2. Find the total salary expense per department
3. Calculate running totals of daily revenue
4. Sum values conditionally using CASE