SQL for Analysis

How company data is stored

• A database is one shared place where a company keeps its data. SQL is how you ask it questions.
• Each table is about one subject: customers, invoices or invoice lines.
• A row is one record. A column is one fact about it.
• A key identifies exactly one row, and other tables use it to link to that row.
• Every column has a type: text, date or number.

Common mistake

Counting rows as if each row were an invoice. In a table of invoice lines, one invoice can take several rows, so counting rows gives too many invoices and too low an average. Check what one row means first.

SELECT and FROM

• SELECT picks the columns. FROM names the table.
• Commas between columns, none after the last.
• * after SELECT means every column. Between numbers, it means multiply.
• SQL can calculate: + - * /. Use 4.0 to get decimals.

SELECT column1, column2
FROM table_name;

SELECT number * 1.0 / number AS name;

SELECT customer_ref, country
FROM customers;

SELECT 18479223 * 1.0 / 3029 AS average;

Common mistake

Forgetting a comma between columns. SELECT customer_ref country returns only one column, with no error. Put a comma between every pair of columns.

WHERE: choosing rows

• WHERE keeps only rows that pass a test.
• Text and dates in quotes, numbers without.
• AND: both true. OR: either true. IN: any of a list. BETWEEN: a range, both ends included.

SELECT columns
FROM table_name
WHERE condition
  AND other_condition;

SELECT invoice_ref, net_amount
FROM customer_invoices
WHERE net_amount > 50000
  AND invoice_date BETWEEN '2025-07-01' AND '2025-09-30';

Common mistake

Using AND when you mean OR. "Customers in the Netherlands and Belgium" sounds like AND, but no customer is in both countries at once, so AND finds nobody. Use IN ('NL', 'BE').

ORDER BY and LIMIT

• ORDER BY sorts. Smallest first unless you add DESC.
• LIMIT keeps the first rows.
• Clause order: SELECT, FROM, WHERE, ORDER BY, LIMIT.

SELECT columns
FROM table_name
ORDER BY column DESC
LIMIT n;

SELECT invoice_ref, net_amount
FROM customer_invoices
ORDER BY net_amount DESC
LIMIT 5;

Common mistake

Forgetting DESC. Without it, sorting goes smallest first, so a "top 5" quietly gives you the 5 smallest. Add DESC after the column.

DISTINCT, COUNT and aliases

• DISTINCT lists each value, or each combination, once.
• COUNT(*) counts rows. COUNT(DISTINCT col) counts different values.
• AS names a result column.

SELECT COUNT(DISTINCT column) AS name
FROM table_name;

SELECT COUNT(DISTINCT country) AS countries
FROM customers;

Common mistake

Using DISTINCT to make repeats disappear. If rows repeat when you did not expect it, find out why first. It usually means one row is not what you thought.

How company data is stored

• A database is one shared place where a company keeps its data. SQL is how you ask it questions.
• Each table is about one subject: customers, invoices or invoice lines.
• A row is one record. A column is one fact about it.
• A key identifies exactly one row, and other tables use it to link to that row.
• Every column has a type: text, date or number.

Common mistake

Counting rows as if each row were an invoice. In a table of invoice lines, one invoice can take several rows, so counting rows gives too many invoices and too low an average. Check what one row means first.

SELECT and FROM

• SELECT picks the columns. FROM names the table.
• Commas between columns, none after the last.
• * after SELECT means every column. Between numbers, it means multiply.
• SQL can calculate: + - * /. Use 4.0 to get decimals.

SELECT column1, column2
FROM table_name;

SELECT number * 1.0 / number AS name;

SELECT customer_ref, country
FROM customers;

SELECT 18479223 * 1.0 / 3029 AS average;

Common mistake

Forgetting a comma between columns. SELECT customer_ref country returns only one column, with no error. Put a comma between every pair of columns.

WHERE: choosing rows

• WHERE keeps only rows that pass a test.
• Text and dates in quotes, numbers without.
• AND: both true. OR: either true. IN: any of a list. BETWEEN: a range, both ends included.

SELECT columns
FROM table_name
WHERE condition
  AND other_condition;

SELECT invoice_ref, net_amount
FROM customer_invoices
WHERE net_amount > 50000
  AND invoice_date BETWEEN '2025-07-01' AND '2025-09-30';

Common mistake

Using AND when you mean OR. "Customers in the Netherlands and Belgium" sounds like AND, but no customer is in both countries at once, so AND finds nobody. Use IN ('NL', 'BE').

ORDER BY and LIMIT

• ORDER BY sorts. Smallest first unless you add DESC.
• LIMIT keeps the first rows.
• Clause order: SELECT, FROM, WHERE, ORDER BY, LIMIT.

SELECT columns
FROM table_name
ORDER BY column DESC
LIMIT n;

SELECT invoice_ref, net_amount
FROM customer_invoices
ORDER BY net_amount DESC
LIMIT 5;

Common mistake

Forgetting DESC. Without it, sorting goes smallest first, so a "top 5" quietly gives you the 5 smallest. Add DESC after the column.

DISTINCT, COUNT and aliases

• DISTINCT lists each value, or each combination, once.
• COUNT(*) counts rows. COUNT(DISTINCT col) counts different values.
• AS names a result column.

SELECT COUNT(DISTINCT column) AS name
FROM table_name;

SELECT COUNT(DISTINCT country) AS countries
FROM customers;

Common mistake

Using DISTINCT to make repeats disappear. If rows repeat when you did not expect it, find out why first. It usually means one row is not what you thought.

Name the grain

• The grain is what one row stands for: "one row is one…".
• Count rows only in a table whose grain matches what you are counting.

Common mistake

Counting rows in the wrong table. Order lines are not orders. Name the grain before you count.

Is it really a key?

• A key never repeats.
• Test: COUNT(*) equals COUNT(DISTINCT column).
• Some keys need two columns. Glue them with || to test.

SELECT COUNT(*),
       COUNT(DISTINCT column)
FROM table_name;

SELECT COUNT(DISTINCT so_ref || '-' || line_no)
FROM sales_order_lines;

Common mistake

Assuming a reference is a key. so_ref is a key of orders but repeats in order lines. Test it in the table you are using.

Find the repeats

• GROUP BY makes one group per value.
• HAVING filters groups. WHERE filters rows.

SELECT column, COUNT(*)
FROM table_name
GROUP BY column
HAVING COUNT(*) > 1;

SELECT customer_ref, COUNT(*) AS invoices
FROM customer_invoices
GROUP BY customer_ref
HAVING COUNT(*) > 100;

Common mistake

Using WHERE for a count. WHERE COUNT(*) > 1 fails, because counts only exist after grouping. Use HAVING.

Tables with history

• History tables add a new row or version instead of overwriting.
• The highest version is the current status.
• Decide which version you mean before you count.

SELECT COUNT(*)
FROM table_name
WHERE status = 'WITHDRAWN';

SELECT COUNT(*) AS withdrawn
FROM product_revisions
WHERE revision_status = 'WITHDRAWN';

Common mistake

Counting every row. 3,176 rows are 3,110 revisions, of which 3,044 are in use, for 2,830 products.

Name the grain

• The grain is what one row stands for: "one row is one…".
• Count rows only in a table whose grain matches what you are counting.

Common mistake

Counting rows in the wrong table. Order lines are not orders. Name the grain before you count.

Is it really a key?

• A key never repeats.
• Test: COUNT(*) equals COUNT(DISTINCT column).
• Some keys need two columns. Glue them with || to test.

SELECT COUNT(*),
       COUNT(DISTINCT column)
FROM table_name;

SELECT COUNT(DISTINCT so_ref || '-' || line_no)
FROM sales_order_lines;

Common mistake

Assuming a reference is a key. so_ref is a key of orders but repeats in order lines. Test it in the table you are using.

Find the repeats

• GROUP BY makes one group per value.
• HAVING filters groups. WHERE filters rows.

SELECT column, COUNT(*)
FROM table_name
GROUP BY column
HAVING COUNT(*) > 1;

SELECT customer_ref, COUNT(*) AS invoices
FROM customer_invoices
GROUP BY customer_ref
HAVING COUNT(*) > 100;

Common mistake

Using WHERE for a count. WHERE COUNT(*) > 1 fails, because counts only exist after grouping. Use HAVING.

Tables with history

• History tables add a new row or version instead of overwriting.
• The highest version is the current status.
• Decide which version you mean before you count.

SELECT COUNT(*)
FROM table_name
WHERE status = 'WITHDRAWN';

SELECT COUNT(*) AS withdrawn
FROM product_revisions
WHERE revision_status = 'WITHDRAWN';

Common mistake

Counting every row. 3,176 rows are 3,110 revisions, of which 3,044 are in use, for 2,830 products.

Blanks: NULL

• NULL means no value recorded. It is not zero.
• Find it with IS NULL / IS NOT NULL.
• = and <> skip NULL rows.

WHERE column IS NULL
WHERE column <> value
   OR column IS NULL

SELECT COUNT(*)
FROM enquiries
WHERE campaign_ref IS NULL;

Common mistake

Writing = NULL. It never matches. Use IS NULL.

Leave out what doesn't count

• Exclude cancelled, test and internal records on purpose.
• Count what you excluded, and say so.

WHERE status <> 'CANCELLED'

SELECT COUNT(*)
FROM sales_order_lines
WHERE line_status <> 'CANCELLED';

Common mistake

Excluding silently. Others cannot reproduce your number. Add a footnote with the count and value removed.

Agree what it counts

• The same word can mean different events: ordered, invoiced, paid.
• Agree the event, the date and the exclusions.

Common mistake

Comparing numbers with different definitions. €22.3m ordered and €18.5m invoiced are both right for Q3.

Date boundaries

• Use >= start and < next start.
• BETWEEN includes both ends, which is risky with timestamps.

WHERE date_col >= 'start'
  AND date_col <  'next_start'

SELECT COUNT(*)
FROM customer_invoices
WHERE invoice_date >= '2025-09-01'
  AND invoice_date <  '2025-10-01';

Common mistake

Losing the last day. With timestamps, BETWEEN … '2025-09-30' stops at midnight on the 30th.

Blanks: NULL

• NULL means no value recorded. It is not zero.
• Find it with IS NULL / IS NOT NULL.
• = and <> skip NULL rows.

WHERE column IS NULL
WHERE column <> value
   OR column IS NULL

SELECT COUNT(*)
FROM enquiries
WHERE campaign_ref IS NULL;

Common mistake

Writing = NULL. It never matches. Use IS NULL.

Leave out what doesn't count

• Exclude cancelled, test and internal records on purpose.
• Count what you excluded, and say so.

WHERE status <> 'CANCELLED'

SELECT COUNT(*)
FROM sales_order_lines
WHERE line_status <> 'CANCELLED';

Common mistake

Excluding silently. Others cannot reproduce your number. Add a footnote with the count and value removed.

Agree what it counts

• The same word can mean different events: ordered, invoiced, paid.
• Agree the event, the date and the exclusions.

Common mistake

Comparing numbers with different definitions. €22.3m ordered and €18.5m invoiced are both right for Q3.

Date boundaries

• Use >= start and < next start.
• BETWEEN includes both ends, which is risky with timestamps.

WHERE date_col >= 'start'
  AND date_col <  'next_start'

SELECT COUNT(*)
FROM customer_invoices
WHERE invoice_date >= '2025-09-01'
  AND invoice_date <  '2025-10-01';

Common mistake

Losing the last day. With timestamps, BETWEEN … '2025-09-30' stops at midnight on the 30th.

Three ways to count

• COUNT(*): rows.
• COUNT(column): rows where column is not NULL.
• COUNT(DISTINCT column): different values.

SELECT COUNT(*), COUNT(column),
       COUNT(DISTINCT column)
FROM table_name;

SELECT COUNT(*), COUNT(campaign_ref),
       COUNT(DISTINCT campaign_ref)
FROM enquiries;

Common mistake

Using COUNT(column) to count rows. It skips blanks: 208, not 328.

SUM, AVG and GROUP BY

• SUM AVG MIN MAX summarise a column.
• With GROUP BY you get one summary per group.
• strftime('%Y-%m', date) gives the month.

SELECT group_col, SUM(value)
FROM table_name
GROUP BY group_col;

SELECT strftime('%Y-%m', invoice_date) AS month,
       SUM(net_amount) AS invoiced
FROM customer_invoices
GROUP BY month;

Common mistake

A column that is neither grouped nor summarised. Every SELECT column must be in GROUP BY or inside SUM, COUNT, AVG…

Averages that mislead

• A few large values pull an average up.
• Report averages per group when groups differ.
• Divide totals rather than averaging averages.

Example

SELECT SUM(net_amount) / COUNT(*)
FROM customer_invoices;

Common mistake

Averaging averages. €4,784 instead of the true €5,115.

Shares, per cent and points

• CASE WHEN … THEN 1 ELSE 0 END counts matching rows.
• Share = part × 100.0 ÷ total.
• Changes in shares are points.

SELECT SUM(CASE WHEN condition
           THEN 1 ELSE 0 END) * 100.0
       / COUNT(*)
FROM table_name;

SELECT SUM(CASE WHEN channel = 'WEBSHOP'
           THEN 1 ELSE 0 END) * 100.0
       / COUNT(*)
FROM sales_orders;

Common mistake

Calling a points change a per cent change. 27.5% to 26.7% is 0.8 points.

Match finance's number

• Net = invoiced − credit notes.
• Reconcile step by step and size each step.

Example

SELECT SUM(amount)
FROM credit_notes
WHERE issue_date >= '2025-07-01'
  AND issue_date <  '2025-10-01';

Common mistake

Comparing gross with net. A gap of exactly the credit notes is not an error.

Three ways to count

• COUNT(*): rows.
• COUNT(column): rows where column is not NULL.
• COUNT(DISTINCT column): different values.

SELECT COUNT(*), COUNT(column),
       COUNT(DISTINCT column)
FROM table_name;

SELECT COUNT(*), COUNT(campaign_ref),
       COUNT(DISTINCT campaign_ref)
FROM enquiries;

Common mistake

Using COUNT(column) to count rows. It skips blanks: 208, not 328.

SUM, AVG and GROUP BY

• SUM AVG MIN MAX summarise a column.
• With GROUP BY you get one summary per group.
• strftime('%Y-%m', date) gives the month.

SELECT group_col, SUM(value)
FROM table_name
GROUP BY group_col;

SELECT strftime('%Y-%m', invoice_date) AS month,
       SUM(net_amount) AS invoiced
FROM customer_invoices
GROUP BY month;

Common mistake

A column that is neither grouped nor summarised. Every SELECT column must be in GROUP BY or inside SUM, COUNT, AVG…

Averages that mislead

• A few large values pull an average up.
• Report averages per group when groups differ.
• Divide totals rather than averaging averages.

Example

SELECT SUM(net_amount) / COUNT(*)
FROM customer_invoices;

Common mistake

Averaging averages. €4,784 instead of the true €5,115.

Shares, per cent and points

• CASE WHEN … THEN 1 ELSE 0 END counts matching rows.
• Share = part × 100.0 ÷ total.
• Changes in shares are points.

SELECT SUM(CASE WHEN condition
           THEN 1 ELSE 0 END) * 100.0
       / COUNT(*)
FROM table_name;

SELECT SUM(CASE WHEN channel = 'WEBSHOP'
           THEN 1 ELSE 0 END) * 100.0
       / COUNT(*)
FROM sales_orders;

Common mistake

Calling a points change a per cent change. 27.5% to 26.7% is 0.8 points.

Match finance's number

• Net = invoiced − credit notes.
• Reconcile step by step and size each step.

Example

SELECT SUM(amount)
FROM credit_notes
WHERE issue_date >= '2025-07-01'
  AND issue_date <  '2025-10-01';

Common mistake

Comparing gross with net. A gap of exactly the credit notes is not an error.

How the tables fit

• Event tables (invoices) point to description tables (customers).
• Many invoices to one customer: many to one.

Common mistake

Not knowing which side is "many". That is where double counting starts.

INNER and LEFT JOIN

• JOIN … ON keeps matching rows.
• LEFT JOIN keeps every left row; unmatched get NULL.
• Short table aliases: AS i.

SELECT a.col, b.col
FROM table_a AS a
LEFT JOIN table_b AS b
  ON a.key = b.key;

SELECT c.customer_ref
FROM customers AS c
LEFT JOIN customer_invoices AS i
  ON c.customer_ref = i.customer_ref
WHERE i.invoice_ref IS NULL;

Common mistake

A WHERE on the right table after a LEFT JOIN. It throws away the unmatched rows you wanted, unless you test for IS NULL.

Joins that double count

• Joining to a "many" table repeats the "one" side.
• Sums of repeated columns are inflated, with no error.

Common mistake

Summing invoice amounts after joining to lines. €30.9m instead of €18.5m.

Fix the double count

• Sum the measure of the most detailed table.
• Count with DISTINCT on the key.
• SUM(DISTINCT …) is not a fix.

SELECT SUM(detail.amount)
FROM header
JOIN detail ON header.key = detail.key;

SELECT SUM(l.line_amount)
FROM customer_invoices AS i
JOIN customer_invoice_lines AS l
  ON i.invoice_ref = l.invoice_ref;

Common mistake

SUM(DISTINCT amount). Equal amounts on different invoices are merged.

How the tables fit

• Event tables (invoices) point to description tables (customers).
• Many invoices to one customer: many to one.

Common mistake

Not knowing which side is "many". That is where double counting starts.

INNER and LEFT JOIN

• JOIN … ON keeps matching rows.
• LEFT JOIN keeps every left row; unmatched get NULL.
• Short table aliases: AS i.

SELECT a.col, b.col
FROM table_a AS a
LEFT JOIN table_b AS b
  ON a.key = b.key;

SELECT c.customer_ref
FROM customers AS c
LEFT JOIN customer_invoices AS i
  ON c.customer_ref = i.customer_ref
WHERE i.invoice_ref IS NULL;

Common mistake

A WHERE on the right table after a LEFT JOIN. It throws away the unmatched rows you wanted, unless you test for IS NULL.

Joins that double count

• Joining to a "many" table repeats the "one" side.
• Sums of repeated columns are inflated, with no error.

Common mistake

Summing invoice amounts after joining to lines. €30.9m instead of €18.5m.

Fix the double count

• Sum the measure of the most detailed table.
• Count with DISTINCT on the key.
• SUM(DISTINCT …) is not a fix.

SELECT SUM(detail.amount)
FROM header
JOIN detail ON header.key = detail.key;

SELECT SUM(l.line_amount)
FROM customer_invoices AS i
JOIN customer_invoice_lines AS l
  ON i.invoice_ref = l.invoice_ref;

Common mistake

SUM(DISTINCT amount). Equal amounts on different invoices are merged.