SQL Joins and GROUP BY with Finance Examples

In finance, the numbers you need almost never sit in one table. Customer names are in the customer master, bills are in the invoice table and money received is in the payments table. SQL joins bring these tables together, and GROUP BY turns thousands of rows into a short summary, like “total billed per customer”. In this lesson we build up, step by step, to a real receivables ageing report, the kind your manager asks for at every month end.

What you will learn

• A quick refresher of SELECT, WHERE and ORDER BY
• INNER, LEFT, RIGHT and FULL joins, and when to use each one
• How GROUP BY works with SUM, COUNT and AVG, and how HAVING filters the groups
• How to build a 0 to 30, 31 to 60, 61 to 90 and 90+ days ageing report with CASE
• The two mistakes that give wrong totals: duplicate rows after a join, and NULL values

Our sample data

We will use three small tables from a made-up distributor. They are tiny on purpose, so you can check every answer by hand.

A table is like one Excel sheet with fixed column names. Each row is one record, for example one invoice. A key is a column that identifies a row, like customer_id.

customers (the customer master)

customer_id	customer_name	city
C01	Sharma Traders	Delhi
C02	Gupta Electricals	Mumbai
C03	Patel Textiles	Ahmedabad
C04	Reddy Foods	Hyderabad

invoices (bills we raised, amounts in rupees)

invoice_id	customer_id	invoice_date	amount
INV101	C03	2025-12-15	25000
INV102	C01	2026-01-10	50000
INV103	C02	2026-01-25	120000
INV104	C01	2026-02-15	30000
INV105	C03	2026-03-05	80000
INV106	C02	2026-03-20	45000
INV107	C05	2026-03-25	20000

payments (money received against invoices)

payment_id	invoice_id	payment_date	amount_paid
P01	INV102	2026-02-05	50000
P02	INV103	2026-02-20	60000
P03	INV103	2026-03-15	30000
P04	INV105	2026-03-28	20000

Look closely, because the data has three small traps, just like real data:

• Reddy Foods (C04) is in the master but has no invoices yet.
• INV107 belongs to customer C05, who is not in the customer master. This happens when someone bills before the master is set up.
• INV103 was paid in two parts, so it has two payment rows.

Run this script once to create the tables. It works in PostgreSQL, MySQL 8 and SQL Server.

CREATE TABLE customers (
  customer_id   VARCHAR(5) PRIMARY KEY,
  customer_name VARCHAR(50),
  city          VARCHAR(30)
);

CREATE TABLE invoices (
  invoice_id   VARCHAR(10) PRIMARY KEY,
  customer_id  VARCHAR(5),
  invoice_date DATE,
  amount       DECIMAL(12,2)
);

CREATE TABLE payments (
  payment_id   VARCHAR(5) PRIMARY KEY,
  invoice_id   VARCHAR(10),
  payment_date DATE,
  amount_paid  DECIMAL(12,2)
);

INSERT INTO customers VALUES
  ('C01', 'Sharma Traders',    'Delhi'),
  ('C02', 'Gupta Electricals', 'Mumbai'),
  ('C03', 'Patel Textiles',    'Ahmedabad'),
  ('C04', 'Reddy Foods',       'Hyderabad');

INSERT INTO invoices VALUES
  ('INV101', 'C03', '2025-12-15',  25000),
  ('INV102', 'C01', '2026-01-10',  50000),
  ('INV103', 'C02', '2026-01-25', 120000),
  ('INV104', 'C01', '2026-02-15',  30000),
  ('INV105', 'C03', '2026-03-05',  80000),
  ('INV106', 'C02', '2026-03-20',  45000),
  ('INV107', 'C05', '2026-03-25',  20000);

INSERT INTO payments VALUES
  ('P01', 'INV102', '2026-02-05', 50000),
  ('P02', 'INV103', '2026-02-20', 60000),
  ('P03', 'INV103', '2026-03-15', 30000),
  ('P04', 'INV105', '2026-03-28', 20000);

Refresher: SELECT, WHERE and ORDER BY

Every query you write in this lesson is built from the same few parts:

• SELECT says which columns you want to see.
• FROM says which table to read.
• WHERE keeps only the rows that pass a condition.
• ORDER BY sorts the result. DESC means largest first, ASC (the default) means smallest first.

Here is a simple one: show invoices above 40,000 rupees, biggest first.

SELECT invoice_id, customer_id, amount
FROM invoices
WHERE amount > 40000
ORDER BY amount DESC;

Result:

invoice_id	customer_id	amount
INV103	C02	120000
INV105	C03	80000
INV102	C01	50000
INV106	C02	45000

WHERE throws away INV101, INV104 and INV107 because they are 40,000 or less, and ORDER BY sorts what is left.

The order SQL really follows

You write SELECT first, but the database does not run it first. It reads the tables, filters, groups, and only then picks the columns and sorts. Knowing this order explains many error messages later in the lesson, so keep this chart in mind. Read it from top to bottom, one box at a time.

Joins: bringing two tables together

A join matches rows from two tables using a column they share. Our invoices table has customer_id but no customer name. The customers table has the name. So we join them ON customer_id.

We also give each table a short nickname, called an alias: invoices AS i and customers AS c. Then i.amount means “amount from invoices”, and c.customer_name means “name from customers”. This saves typing and avoids confusion when both tables have a column with the same name.

INNER JOIN: only rows that match in both tables

INNER JOIN keeps a row only when it finds a partner in both tables.

SELECT i.invoice_id, c.customer_name, i.amount
FROM invoices AS i
INNER JOIN customers AS c
  ON i.customer_id = c.customer_id
ORDER BY i.invoice_id;

Result:

invoice_id	customer_name	amount
INV101	Patel Textiles	25000
INV102	Sharma Traders	50000
INV103	Gupta Electricals	120000
INV104	Sharma Traders	30000
INV105	Patel Textiles	80000
INV106	Gupta Electricals	45000

Count the rows. We had 7 invoices, but only 6 came back. INV107 is missing because its customer C05 is not in the master. Reddy Foods is missing too, because it has no invoice.

This is why INNER JOIN can be dangerous in finance: your invoice total just dropped by 20,000 rupees without any warning. Plain JOIN means the same as INNER JOIN.

LEFT JOIN: keep every row of the first table

LEFT JOIN keeps every row from the table written first (the “left” table), even when there is no partner. Where there is no partner, the columns from the other table come back empty. In SQL an empty value is called NULL, which means “no value”, not zero.

Let us list every customer with their invoices:

SELECT c.customer_name, i.invoice_id, i.amount
FROM customers AS c
LEFT JOIN invoices AS i
  ON c.customer_id = i.customer_id
ORDER BY c.customer_name, i.invoice_id;

Result:

customer_name	invoice_id	amount
Gupta Electricals	INV103	120000
Gupta Electricals	INV106	45000
Patel Textiles	INV101	25000
Patel Textiles	INV105	80000
Reddy Foods	NULL	NULL
Sharma Traders	INV102	50000
Sharma Traders	INV104	30000

Now Reddy Foods appears, with NULL in the invoice columns. This is the query you use to answer “which customers have not been billed this month?”. Just add WHERE i.invoice_id IS NULL.

RIGHT JOIN: keep every row of the second table

RIGHT JOIN is the mirror image. It keeps every row from the table written second.

SELECT c.customer_name, i.invoice_id, i.amount
FROM customers AS c
RIGHT JOIN invoices AS i
  ON c.customer_id = i.customer_id
ORDER BY i.invoice_id;

Result:

customer_name	invoice_id	amount
Patel Textiles	INV101	25000
Sharma Traders	INV102	50000
Gupta Electricals	INV103	120000
Sharma Traders	INV104	30000
Patel Textiles	INV105	80000
Gupta Electricals	INV106	45000
NULL	INV107	20000

Every invoice is here, and INV107 shows a NULL name. That NULL is a real finding: someone billed a customer who is not in the master. Send it to the master data team.

Any RIGHT JOIN can be written as a LEFT JOIN by swapping the two tables, and most people do that because it reads more easily.

FULL OUTER JOIN: keep everything from both sides

FULL OUTER JOIN keeps all rows from both tables. Matches are joined, and anything without a partner still appears, with NULLs on the missing side. It is perfect for reconciliations, where you want to see what is in one list but not the other.

SELECT c.customer_id, c.customer_name,
       i.invoice_id, i.customer_id AS inv_customer, i.amount
FROM customers AS c
FULL OUTER JOIN invoices AS i
  ON c.customer_id = i.customer_id
ORDER BY i.invoice_id;

Result:

customer_id	customer_name	invoice_id	inv_customer	amount
C04	Reddy Foods	NULL	NULL	NULL
C03	Patel Textiles	INV101	C03	25000
C01	Sharma Traders	INV102	C01	50000
C02	Gupta Electricals	INV103	C02	120000
C01	Sharma Traders	INV104	C01	30000
C03	Patel Textiles	INV105	C03	80000
C02	Gupta Electricals	INV106	C02	45000
NULL	NULL	INV107	C05	20000

Both problems show up in one result: Reddy Foods with no invoice at the top, and INV107 with no customer at the bottom. 8 rows in total.

-- MySQL version of a FULL OUTER JOIN
SELECT c.customer_name, i.invoice_id, i.amount
FROM customers AS c
LEFT JOIN invoices AS i ON c.customer_id = i.customer_id
UNION
SELECT c.customer_name, i.invoice_id, i.amount
FROM customers AS c
RIGHT JOIN invoices AS i ON c.customer_id = i.customer_id;

This returns the same 8 rows (with three of the columns, in any order, because there is no ORDER BY). UNION also removes exact duplicate rows, which is why the matching rows do not appear twice.

Which join should you pick?

Ask yourself two questions, as the chart shows. Follow the arrows down for Yes and to the side for No.

GROUP BY: from rows to a summary

GROUP BY works like a PivotTable in Excel. You choose a column to group on, and SQL squeezes all rows with the same value into one row. Then you use an aggregate function to say what to do with the numbers in each group:

• SUM(amount) adds them
• COUNT(*) counts the rows
• AVG(amount) gives the average
• MIN() and MAX() give the smallest and largest

Total billed per customer:

SELECT customer_id,
       COUNT(*)    AS invoice_count,
       SUM(amount) AS total_billed,
       AVG(amount) AS avg_invoice
FROM invoices
GROUP BY customer_id
ORDER BY total_billed DESC;

Result:

customer_id	invoice_count	total_billed	avg_invoice
C02	2	165000	82500
C03	2	105000	52500
C01	2	80000	40000
C05	1	20000	20000

Check one by hand: C02 has INV103 (120,000) and INV106 (45,000). Total 165,000, average 82,500. Correct.

AS total_billed gives the result column a friendly name. This new name is called a column alias.

Join and group together

C05 above is just a code. Let us show names, and include customers with no bills, by joining first and then grouping.

SELECT c.customer_name,
       COUNT(i.invoice_id)       AS invoice_count,
       COALESCE(SUM(i.amount), 0) AS total_billed
FROM customers AS c
LEFT JOIN invoices AS i
  ON c.customer_id = i.customer_id
GROUP BY c.customer_name
ORDER BY total_billed DESC;

Result:

customer_name	invoice_count	total_billed
Gupta Electricals	2	165000
Patel Textiles	2	105000
Sharma Traders	2	80000
Reddy Foods	0	0

Two small details matter here:

• COUNT(i.invoice_id) counts only rows where invoice_id has a value. COUNT(*) counts every row, so it would wrongly show 1 invoice for Reddy Foods, because the LEFT JOIN gave it one row full of NULLs.
• COALESCE(SUM(i.amount), 0) turns a NULL total into 0. COALESCE returns the first value in its list that is not NULL. It works the same in all three databases.

HAVING: filter the groups

WHERE filters rows before grouping. HAVING filters groups after grouping. So if your condition uses SUM, COUNT or AVG, it must go in HAVING.

Customers billed more than 1 lakh in total:

SELECT customer_id, SUM(amount) AS total_billed
FROM invoices
GROUP BY customer_id
HAVING SUM(amount) > 100000
ORDER BY total_billed DESC;

Result:

customer_id	total_billed
C02	165000
C03	105000

Now use both together. Only count invoices from 1 January 2026, then keep customers above 70,000:

SELECT customer_id, SUM(amount) AS total_billed
FROM invoices
WHERE invoice_date >= '2026-01-01'
GROUP BY customer_id
HAVING SUM(amount) > 70000
ORDER BY total_billed DESC, customer_id;

Result:

customer_id	total_billed
C02	165000
C01	80000
C03	80000

See what happened to C03. In the earlier query it had 105,000. Here WHERE removed INV101 (dated December 2025) before grouping, so only INV105 (80,000) was left. C05 has only 20,000, so HAVING removed it. The second sort column, customer_id, decides the order when two totals are equal.

Project: a receivables ageing report

An ageing report shows how much each customer still owes, split by how old the bill is. Credit control teams chase the 90+ days column first, and auditors look at it for doubtful debts. We will build it in three steps, using our tables and an “as of” date of 31 March 2026, the financial year end.

Follow the chart. Each box is one part of the final query.

Step 1: balance due on each invoice

We first add up payments per invoice in a small temporary result, then join it to invoices. The WITH paid AS (...) part is a CTE (common table expression). Think of it as a named helper table that lives only while the query runs.

WITH paid AS (
  SELECT invoice_id, SUM(amount_paid) AS total_paid
  FROM payments
  GROUP BY invoice_id
)
SELECT i.invoice_id,
       i.amount,
       COALESCE(p.total_paid, 0)            AS total_paid,
       i.amount - COALESCE(p.total_paid, 0) AS balance
FROM invoices AS i
LEFT JOIN paid AS p
  ON i.invoice_id = p.invoice_id
ORDER BY i.invoice_id;

Result:

invoice_id	amount	total_paid	balance
INV101	25000	0	25000
INV102	50000	50000	0
INV103	120000	90000	30000
INV104	30000	0	30000
INV105	80000	20000	60000
INV106	45000	0	45000
INV107	20000	0	20000

INV103’s two payments (60,000 and 30,000) were added up to 90,000 before the join, so INV103 still appears only once. INV102 is fully paid.

Step 2: days old and the bucket

Now we work out the age of each open invoice and give it a label with CASE. CASE checks conditions from top to bottom and returns the label of the first one that is true, like a nested IF in Excel.

Subtracting dates is the one place where the three databases differ:

Database	Days between the report date and the invoice date
PostgreSQL	DATE '2026-03-31' - i.invoice_date
MySQL 8	DATEDIFF('2026-03-31', i.invoice_date)
SQL Server	DATEDIFF(day, i.invoice_date, '2026-03-31')

The query below uses the PostgreSQL form. Swap that one line for your database.

WITH paid AS (
  SELECT invoice_id, SUM(amount_paid) AS total_paid
  FROM payments
  GROUP BY invoice_id
),
open_items AS (
  SELECT i.invoice_id,
         i.customer_id,
         i.amount - COALESCE(p.total_paid, 0) AS balance,
         DATE '2026-03-31' - i.invoice_date    AS days_old
  FROM invoices AS i
  LEFT JOIN paid AS p
    ON i.invoice_id = p.invoice_id
  WHERE i.amount - COALESCE(p.total_paid, 0) > 0
)
SELECT invoice_id, customer_id, days_old, balance,
       CASE
         WHEN days_old <= 30 THEN '0-30'
         WHEN days_old <= 60 THEN '31-60'
         WHEN days_old <= 90 THEN '61-90'
         ELSE '90+'
       END AS age_bucket
FROM open_items
ORDER BY days_old DESC;

Result:

invoice_id	customer_id	days_old	balance	age_bucket
INV101	C03	106	25000	90+
INV103	C02	65	30000	61-90
INV104	C01	44	30000	31-60
INV105	C03	26	60000	0-30
INV106	C02	11	45000	0-30
INV107	C05	6	20000	0-30

INV102 is gone because its balance is zero. Why does WHEN days_old <= 60 not also catch a 20 day invoice? Because CASE stops at the first true condition, and <= 30 already matched it.

In real use, write CURRENT_DATE (PostgreSQL and MySQL) or CAST(GETDATE() AS DATE) (SQL Server) instead of a fixed date. We use a fixed date so your results match ours.

Step 3: one row per customer, one column per bucket

Finally, we turn buckets into columns. The trick is SUM(CASE ... THEN balance ELSE 0 END): for each bucket column, SQL adds the balance only when the invoice falls in that bucket, and adds 0 otherwise.

WITH paid AS (
  SELECT invoice_id, SUM(amount_paid) AS total_paid
  FROM payments
  GROUP BY invoice_id
),
open_items AS (
  SELECT i.invoice_id,
         i.customer_id,
         i.amount - COALESCE(p.total_paid, 0) AS balance,
         DATE '2026-03-31' - i.invoice_date    AS days_old
  FROM invoices AS i
  LEFT JOIN paid AS p
    ON i.invoice_id = p.invoice_id
  WHERE i.amount - COALESCE(p.total_paid, 0) > 0
)
SELECT COALESCE(c.customer_name, 'Unknown customer') AS customer,
       SUM(CASE WHEN o.days_old <= 30 THEN o.balance ELSE 0 END)            AS days_0_30,
       SUM(CASE WHEN o.days_old BETWEEN 31 AND 60 THEN o.balance ELSE 0 END) AS days_31_60,
       SUM(CASE WHEN o.days_old BETWEEN 61 AND 90 THEN o.balance ELSE 0 END) AS days_61_90,
       SUM(CASE WHEN o.days_old > 90 THEN o.balance ELSE 0 END)             AS days_90_plus,
       SUM(o.balance)                                                       AS total_due
FROM open_items AS o
LEFT JOIN customers AS c
  ON o.customer_id = c.customer_id
GROUP BY COALESCE(c.customer_name, 'Unknown customer')
ORDER BY total_due DESC;

Result:

customer	days_0_30	days_31_60	days_61_90	days_90_plus	total_due
Patel Textiles	60000	0	0	25000	85000
Gupta Electricals	45000	0	30000	0	75000
Sharma Traders	0	30000	0	0	30000
Unknown customer	20000	0	0	0	20000

The total due is 2,10,000 rupees. Check it against Step 1: 25,000 + 30,000 + 30,000 + 60,000 + 45,000 + 20,000 = 2,10,000. It matches, so nothing was lost or counted twice. We used LEFT JOIN to customers so INV107 stays in the report as “Unknown customer”. With an INNER JOIN, 20,000 rupees of receivables would quietly vanish.

Common mistakes

Duplicate rows after a join

This is the most common way finance reports go wrong. Suppose you join invoices straight to payments and add up both:

-- Wrong: invoice amounts get counted twice
SELECT i.customer_id,
       SUM(i.amount)      AS total_billed,
       SUM(p.amount_paid) AS total_paid
FROM invoices AS i
LEFT JOIN payments AS p
  ON i.invoice_id = p.invoice_id
GROUP BY i.customer_id
ORDER BY i.customer_id;

Result:

customer_id	total_billed	total_paid
C01	80000	50000
C02	285000	90000
C03	105000	20000
C05	20000	NULL

Gupta Electricals (C02) shows 2,85,000 billed, but we only billed it 1,65,000. Here is why. INV103 has two payments, so the join made two rows for INV103, and each row carried the full 1,20,000:

invoice_id	amount	payment_id	amount_paid
INV103	120000	P02	60000
INV103	120000	P03	30000

120,000 + 120,000 + 45,000 = 2,85,000. The fix is what we did in Step 1: add up the “many” table (payments) per invoice first, in a CTE, and join after that. A simple check: count the rows of your main table before the join and after it. If the number grew, look for this problem.

Treating NULL as zero

Any sum with NULL in it gives NULL. If you skip COALESCE in Step 1, every unpaid invoice shows a blank balance instead of its full amount:

-- Wrong: balance becomes NULL for unpaid invoices
WITH paid AS (
  SELECT invoice_id, SUM(amount_paid) AS total_paid
  FROM payments
  GROUP BY invoice_id
)
SELECT i.invoice_id, i.amount, p.total_paid,
       i.amount - p.total_paid AS balance
FROM invoices AS i
LEFT JOIN paid AS p
  ON i.invoice_id = p.invoice_id
ORDER BY i.invoice_id;

INV101, INV104, INV106 and INV107 come back with NULL as total_paid and NULL as balance. These four invoices, worth 1,20,000 rupees, would drop out of the ageing report, because NULL > 0 is never true. Wrap columns from the joined table in COALESCE(column, 0).

More mistakes to watch for

• Using = NULL. WHERE i.invoice_id = NULL never matches anything. Write IS NULL or IS NOT NULL.
• Filtering a LEFT JOIN in WHERE. LEFT JOIN invoices AS i ... WHERE i.amount > 40000 removes the customers with no invoices, because their amount is NULL. Your LEFT JOIN has silently become an INNER JOIN. Put the condition in the ON part instead: ON c.customer_id = i.customer_id AND i.amount > 40000.
• COUNT(*) after a LEFT JOIN. It counts the empty row too. Count a column from the joined table, like COUNT(i.invoice_id).
• Putting an aggregate in WHERE. WHERE SUM(amount) > 100000 gives an error. Use HAVING.

Quick recap

• SELECT, FROM, WHERE and ORDER BY are the base of every query. The database runs FROM and WHERE first and SELECT near the end.
• INNER JOIN keeps only matches, LEFT JOIN keeps the whole first table, RIGHT JOIN the whole second table, FULL OUTER JOIN everything. MySQL needs UNION for a full join.
• GROUP BY with SUM, COUNT and AVG summarises rows. WHERE filters rows before grouping, HAVING filters groups after.
• An ageing report is: total payments per invoice, balance, days old, CASE buckets, then SUM(CASE …) per customer.
• Add up the “many” table before joining, and use COALESCE so NULL does not hide money.

Try it yourself

Use the three sample tables from this lesson.

1. Write a LEFT JOIN that lists every invoice with the customer’s city. Which invoice has no city, and why?
2. Find the total amount received per customer name from the payments table. You will need two joins: payments to invoices, then invoices to customers. Your answer should be Gupta Electricals 90,000, Sharma Traders 50,000 and Patel Textiles 20,000.
3. Change the report date in the ageing query to 30 April 2026 and run it again. Which invoices move into an older bucket? Does the total due change?

Where to go next

• SQL and Data Querying for the rest of our SQL lessons
• Build a P&L Dashboard in Power BI to show your query results on a dashboard
• Power BI Power Query: Import and Clean Data to load SQL tables into Power BI
• Order to Cash OTC to see where receivables and ageing fit in the finance process