Total Money Spent by Each Customer: SUM with GROUP BY

• GROUP BY customer_id – collapses all order rows belonging to the same customer into one output row.
• SUM(total_amount) – adds every order amount within each customer's group to produce the lifetime spend.
• LEFT JOIN – joins from customers to orders so that customers with zero orders still appear in the result.
• COALESCE(SUM(...), 0) – converts the NULL that SUM returns for an empty group into a clean zero for reporting.
• ORDER BY total_spent DESC – ranks customers from highest spender to lowest, which is the typical business requirement.

What does "total money spent by each customer" mean?

This query computes the lifetime spend for every customer by summing all their order amounts. It is one of the most common aggregation queries in banking and finance. At the National Bank of India, Priya uses this report to assign cashback tiers in the loyalty program – platinum for customers who spent over ten lakh, gold for over five lakh, and silver for the rest.

-- Sample tables
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    id           SERIAL PRIMARY KEY,
    customer_id  INTEGER REFERENCES customers(customer_id),
    total_amount DECIMAL(15,2) NOT NULL,
    order_date   DATE NOT NULL
);

INSERT INTO customers (customer_id, name) VALUES
(1001, 'Rajesh Kumar'), (1002, 'Priya Sharma'), (1003, 'Pankaj Gupta');

INSERT INTO orders (customer_id, total_amount, order_date) VALUES
(1001, 45000.00, '2026-01-15'),
(1001, 78000.00, '2026-02-10'),
(1002, 32000.00, '2026-01-22'),
(1002, 91000.00, '2026-03-05');
-- Pankaj (1003) has no orders

How do you write the basic query?

The minimal query groups the orders table by customer and sums the amount. Ordering by total descending gives a "biggest spenders first" ranking.

SELECT customer_id,
       SUM(total_amount) AS total_spent
FROM   orders
GROUP BY customer_id
ORDER BY total_spent DESC;

 customer_id | total_spent
-------------+------------
        1001 |   123000.00
        1002 |   123000.00

Notice that Pankaj (1003) does not appear because he has no orders and the query only scans the orders table.

How do you include the customer name?

Join to the customers table and include the name in both the SELECT and the GROUP BY.

SELECT c.customer_id, c.name,
       SUM(o.total_amount) AS total_spent
FROM   customers c
JOIN   orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY total_spent DESC;

 customer_id | name          | total_spent
-------------+---------------+------------
        1001 | Rajesh Kumar  |   123000.00
        1002 | Priya Sharma  |   123000.00

How do you include customers who have never spent?

Switch to a LEFT JOIN from customers to orders. Customers with zero orders produce a NULL for SUM(total_amount), so wrap it in COALESCE to show zero instead.

SELECT c.customer_id, c.name,
       COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM   customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY total_spent DESC;

 customer_id | name          | total_spent
-------------+---------------+------------
        1001 | Rajesh Kumar  |   123000.00
        1002 | Priya Sharma  |   123000.00
        1003 | Pankaj Gupta  |        0.00

Why does SUM return NULL for empty groups?

SUM over no rows returns NULL, not zero. This is defined by the SQL standard. It means that without COALESCE, a customer with no orders would show a blank total on the report, which is confusing.

-- Demonstrating SUM over no rows
SELECT SUM(total_amount) FROM orders WHERE 1 = 0;

 sum
------
 NULL

-- Fix: COALESCE turns NULL into 0
SELECT COALESCE(SUM(total_amount), 0) FROM orders WHERE 1 = 0;

 coalesce
----------
     0.00

How is INNER JOIN different from LEFT JOIN here?

| Aspect | INNER JOIN | LEFT JOIN |
|---|---|---|
| Customers with orders | Included | Included |
| Customers with zero orders | Excluded | Included with NULL/0 |
| Use case | Only active spenders | Full customer list |
| COALESCE needed | No | Yes, for the SUM |

The choice depends on the business requirement. A loyalty tier report needs all customers (LEFT JOIN). A "top spenders" leaderboard only needs active spenders (INNER JOIN).

What are common mistakes?

The most common mistake is forgetting COALESCE with a LEFT JOIN, which shows NULL instead of zero for non-spenders. Another mistake is summing across mixed currencies without converting first.

-- WRONG: NULL total for customers with no orders
SELECT c.name, SUM(o.total_amount) AS total_spent
FROM   customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name;
-- Pankaj shows total_spent = NULL

-- CORRECT: COALESCE makes it zero
SELECT c.name, COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM   customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name;
-- Pankaj shows total_spent = 0.00

How do you filter to a specific time period?

To find spend in the last 12 months, add a WHERE clause before grouping. Remember that WHERE filters rows and HAVING filters groups.

-- Spend in the last 12 months only
SELECT c.customer_id, c.name,
       COALESCE(SUM(o.total_amount), 0) AS recent_spent
FROM   customers c
LEFT JOIN orders o
       ON o.customer_id = c.customer_id
      AND o.order_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY c.customer_id, c.name
ORDER BY recent_spent DESC;

Notice the date filter is placed in the JOIN condition, not in the WHERE clause. Putting it in WHERE would turn the LEFT JOIN into an INNER JOIN because it would filter out the NULL rows from unmatched customers.

What about performance and indexing?

An index on orders(customer_id) supports the grouping operation. For very large orders tables, Pankaj pre-aggregates lifetime spend into a materialized view refreshed nightly rather than rescanning millions of rows on every dashboard load.

-- Index to support the GROUP BY
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- Materialized view for pre-computed lifetime spend
CREATE MATERIALIZED VIEW mv_customer_spend AS
SELECT customer_id, SUM(total_amount) AS lifetime_spent
FROM   orders
GROUP BY customer_id;

REFRESH MATERIALIZED VIEW mv_customer_spend;

What should a senior engineer know?

• Money should always be DECIMAL/NUMERIC, never FLOAT or DOUBLE, to avoid rounding errors that accumulate across millions of rows.
• If customers can have orders in multiple currencies, you cannot blindly sum – convert to a base currency first or filter to one currency.
• SUM silently ignores NULL amounts, so if some order amounts can be NULL and you want them treated as zero, use SUM(COALESCE(total_amount, 0)).
• For a loyalty tier assignment, combine this query with a CASE expression: CASE WHEN SUM(...) > 1000000 THEN 'PLATINUM' WHEN SUM(...) > 500000 THEN 'GOLD' ELSE 'SILVER' END.