<!-- https://learnsql.cpetoday.com/analyze/excel-to-sql -->
# From Excel to SQL: what you already know

If you can build a pivot table, you already know most of SQL. Every query here is a task you do in Excel today, written the way a database reads it. Nothing is new except the syntax.

The examples use the `cpafirm` sample database: a CPA firm's invoices, payments, invoice lines and general ledger. These are the tables you would otherwise export to a sheet. Each block says which database it runs on, and every result was checked against the sample data before it was published. The queries use SQL Server (T-SQL), with MariaDB notes where the syntax changes. New to the database idea? Read [How databases work](/analyze/how-databases-work) first.

## The cheat sheet

| In Excel | In SQL | Section |
|---|---|---|
| AutoFilter, criteria | `WHERE` | [Filter](#filter-where) |
| Sort A to Z | `ORDER BY` | [Sort](#sort-order-by) |
| Data, Remove Duplicates | `DISTINCT` | [Duplicates](#remove-duplicates-distinct) |
| `VLOOKUP`, `XLOOKUP`, `INDEX`/`MATCH` | `JOIN` | [Lookups](#vlookup-xlookup-and-index-match-join) |
| `#N/A` when nothing matches | `LEFT JOIN` and `NULL` | [No match](#na-left-join-and-null) |
| `SUMIFS`, `COUNTIFS` | `GROUP BY` with `SUM` and `COUNT` | [Summing](#sumifs-and-countifs-group-by) |
| PivotTable | `GROUP BY`, with `CASE` for the columns | [Pivot](#pivottable-group-by-and-case) |
| `IF`, `IFS` | `CASE WHEN` | [IF](#if-and-ifs-case) |
| `IFERROR` | `COALESCE` and `NULLIF` | [IFERROR](#iferror-coalesce-and-nullif) |
| `LEFT`, `MID`, `TRIM`, `&` | `LEFT`, `SUBSTRING`, `TRIM`, `CONCAT` | [Text](#text-left-substring-trim-concat) |
| `EOMONTH`, `YEAR`, date subtraction | `EOMONTH`, `YEAR`, `DATEDIFF` | [Dates](#dates-eomonth-year-datediff) |
| Running total with `$B$2:B2` | `SUM() OVER` | [Running totals](#running-totals-sum-over) |
| `RANK.EQ` | `RANK() OVER` | [Rank](#rank-rank-over) |
| Data, Subtotal | `GROUP BY ROLLUP` | [Subtotals](#subtotals-group-by-rollup) |

## A sheet is a table

A sheet with a header row is already a table. The headers are the column names, each row is one record, and a formula bar is a query. The database adds three things a sheet does not have. Every column has a fixed data type, so a date is always a date. The table can hold far more rows than a sheet. And the query reads only what it asks for, so it does not open the whole file.

The sample tables are bigger than a comfortable spreadsheet. `invoices` has 21,282 rows. `payments` has 20,222. The journal, `gl_journal_lines`, has 93,613 lines. An Excel worksheet holds 1,048,576 rows, so all of these fit on one sheet, but a sheet of that size is slow to filter, sort and recalculate. The database has no such limit to hit.

## Filter: WHERE

In Excel you set an AutoFilter: Status equals "Partial", and Total is at least 1,000. In SQL the same criteria go in `WHERE`, and `AND` joins them.

Runs on `cpafirm`.

```sql
SELECT invoice_number, client_id, issue_date, status, total_amount
FROM invoices
WHERE status = 'Partial'
  AND total_amount >= 1000
ORDER BY total_amount DESC;
```

**What it returns.** The largest partly paid invoice is INV-2024-02815 for 7,905.00, then INV-2023-02833 for 5,740.00 and INV-2025-04157 for 4,700.00. The filter is the same one you would set by hand.

| In Excel | In SQL |
|---|---|
| Text equals "Partial" | `status = 'Partial'` |
| Number at least 1,000 | `total_amount >= 1000` |
| Begins with "INV-2026" | `invoice_number LIKE 'INV-2026%'` |
| Contains "Trust" | `payment_method LIKE '%Trust%'` |
| Is not blank | `client_id IS NOT NULL` |

Note the last row.

> **Watch out.** In SQL a blank cell is `NULL`, and `= NULL` never matches. Test for it with `IS NULL` or `IS NOT NULL`.

> Exercise: List the invoices with the status 'Partial' and a total of at least 1,000. Show the invoice number, client id, issue date and total, sorted by invoice number.


## Sort: ORDER BY

`ORDER BY` is Sort A to Z. Add `DESC` for largest first. Without it, the database returns rows in no particular order. A sheet keeps the order you left it in, but a query has no memory of your last sort. Always put an `ORDER BY` on a result you will read or copy.

## Remove duplicates: DISTINCT

Excel's Remove Duplicates keeps one row per value in the columns you choose. `SELECT DISTINCT` does the same. If you list two columns, the database keeps one row per unique pair, which is the same as choosing both columns in Excel.

To count how many different clients paid in fiscal 2026 (July 2025 to June 2026), remove duplicates and count what is left. Runs on `cpafirm`.

```sql
SELECT COUNT(*) AS clients_who_paid
FROM (SELECT DISTINCT client_id
      FROM payments
      WHERE fiscal_year = 2026) AS paid;
```

**What it returns.** 753 distinct clients with at least one payment in the year. A row count of payments would be much larger, because most clients pay several times.

## VLOOKUP, XLOOKUP and INDEX-MATCH: JOIN

Your lookup formula reads a key from one sheet, finds the matching row on another, and returns a column from it. In SQL this is a `JOIN`. The join condition is the lookup key, and the columns you select can come from either table.

Here is the invoice register with the client's name, for the largest invoices of fiscal 2026. The Excel version would be `=VLOOKUP(B2, Clients!A:C, 2, FALSE)` in a helper column. Runs on `cpafirm`.

```sql
SELECT TOP 5 i.invoice_number, i.issue_date, i.total_amount, c.client_name
FROM invoices AS i
JOIN clients AS c ON c.client_id = i.client_id
WHERE i.fiscal_year = 2026
ORDER BY i.total_amount DESC;
```

**What it returns.** Five invoices, each with the name of its client. The client name is stored once in `clients`, and the join brings it across. MariaDB uses `LIMIT 5` in place of `TOP 5`.

One difference matters. `VLOOKUP` returns the first match. A join returns every match. If the lookup table has the same key twice, the join repeats the invoice. `clients.client_id` is the primary key, so each invoice finds exactly one client. Check your lookup keys for duplicates before you join.

> Diagram: Two tables matched on client_id. The clients table holds client 171 Young, Paul and client 1 Kim, Jennifer. The invoices table holds INV-2026-03848 for client 171 and INV-2025-03477 for client 1. The result adds the client name to each invoice.

## #N/A: LEFT JOIN and NULL

When `VLOOKUP` finds nothing, Excel shows `#N/A`.

> **Watch out.** An inner join drops the row instead, so the item disappears from the report without a warning.

A `LEFT JOIN` keeps every row from the left table and fills the right side with `NULL`, the database word for "no value".

The next query lists four clients and what each was billed in fiscal 2026. Two of them have no invoices that year. Runs on `cpafirm`.

```sql
SELECT c.client_name,
       COUNT(i.invoice_id) AS invoices_fy2026,
       SUM(i.total_amount) AS billed_fy2026
FROM clients AS c
LEFT JOIN invoices AS i
       ON i.client_id = c.client_id
      AND i.fiscal_year = 2026
WHERE c.client_id IN (1, 2, 3, 4)
GROUP BY c.client_name
ORDER BY c.client_name;
```

**What it returns.** Kim, Jennifer: 2 invoices, 950.00. Martin, Maria & Edward: 1 invoice, 600.00. Smith, Angela & Anthony: 0 invoices, and `billed_fy2026` is `NULL`. Wright, Jeffrey: 1 invoice, 1,580.00. The client stays in the list, which is what you want for a report of who was billed and who was not.

> Diagram: Four clients on the left. Three have matching fiscal 2026 invoices and show a count. Smith, Angela and Anthony has no match, so its invoice count is blank, shown as NULL, and the client still appears in the result.

**Put the year in the `ON` clause.** If you move `AND i.fiscal_year = 2026` into `WHERE`, the rows with no invoices are removed and the `LEFT JOIN` behaves like an inner join. This is the most common mistake in lookup-style reports.

Across the whole client list, 239 clients had no invoice at all in fiscal 2026. That is a question for the partner, not a lookup error.

## SUMIFS and COUNTIFS: GROUP BY

`SUMIFS` adds the amounts that match the criteria. In SQL, you name the criteria as the groups and let `SUM` add up each group. `GROUP BY` replaces the list of formulas you would otherwise copy down a column.

Here is fiscal 2026 billing by service line. The amounts sit on `invoice_lines`, and the service line is three links away, so the query walks them in order: line to engagement, engagement to service, and service to service line. That walk is the chain of helper columns you would add in Excel. Runs on `cpafirm`.

```sql
SELECT sl.line_name AS service_line,
       SUM(il.amount) AS revenue,
       COUNT(*) AS invoice_lines
FROM invoice_lines AS il
JOIN invoices AS i       ON i.invoice_id = il.invoice_id
JOIN engagements AS e    ON e.engagement_id = il.engagement_id
JOIN services AS s       ON s.service_id = e.service_id
JOIN service_lines AS sl ON sl.service_line_id = s.service_line_id
WHERE i.fiscal_year = 2026
GROUP BY sl.line_name
ORDER BY revenue DESC;
```

**What it returns.** Six rows. Bookkeeping is 1,395,332.50, Tax Preparation 1,103,875.00, Payroll 494,480.00, Assurance 440,800.00, Advisory 349,833.75 and Tax Planning 135,356.25. The six add to 3,919,677.50, which is exactly the fiscal 2026 invoice total in `invoices`. A total that agrees with the source is a control, and you should check for one every time.

> Diagram: Many invoice lines, each tagged with a service line, collapse into one row per service line. Bookkeeping sums to 1,395,332.50, Tax Preparation to 1,103,875.00 and Payroll to 494,480.00.

`COUNTIFS` works the same way. Put every criterion in `WHERE` and count the rows. This query counts invoices by status and totals each group. Runs on `cpafirm`.

```sql
SELECT status, COUNT(*) AS invoices, SUM(total_amount) AS amount
FROM invoices
GROUP BY status
ORDER BY invoices DESC;
```

**What it returns.** Paid 20,330 invoices for 16,257,058.75. Sent 393 for 373,747.50. Written Off 294 for 223,037.50. Partial 136 for 140,213.75. Overdue 129 for 129,780.00.

## PivotTable: GROUP BY and CASE

A PivotTable puts one field down the rows, another across the columns, and sums a value into each cell. SQL does the row side with `GROUP BY`. For the columns, you write one `CASE` expression for each column you want. The query below pivots fiscal 2026 billing by service line and fiscal quarter. Runs on `cpafirm`.

```sql
SELECT sl.line_name AS service_line,
       SUM(CASE WHEN i.fiscal_quarter = 1 THEN il.amount ELSE 0 END) AS q1,
       SUM(CASE WHEN i.fiscal_quarter = 2 THEN il.amount ELSE 0 END) AS q2,
       SUM(CASE WHEN i.fiscal_quarter = 3 THEN il.amount ELSE 0 END) AS q3,
       SUM(CASE WHEN i.fiscal_quarter = 4 THEN il.amount ELSE 0 END) AS q4,
       SUM(il.amount) AS fiscal_year_total
FROM invoice_lines AS il
JOIN invoices AS i       ON i.invoice_id = il.invoice_id
JOIN engagements AS e    ON e.engagement_id = il.engagement_id
JOIN services AS s       ON s.service_id = e.service_id
JOIN service_lines AS sl ON sl.service_line_id = s.service_line_id
WHERE i.fiscal_year = 2026
GROUP BY sl.line_name
ORDER BY fiscal_year_total DESC;
```

**What it returns.** One row per service line, with the four quarters side by side. Across each row, q1 to q4 add up to `fiscal_year_total`. Check that on every pivot you build. A pivot with a quarter column missing drops that quarter's revenue, and the total will not match the source.

The difference from Excel: a PivotTable adds a new column when a new quarter appears in the data. The SQL column list is fixed, so a new column needs a new line. For a fixed report, that is a feature, because the layout cannot change under a reader.

> Exercise: Pivot the invoices by status for the whole file: one row per status with the number of invoices and the total amount rounded to cents. Largest total first.


## IF and IFS: CASE

`IF` and `IFS` choose a label or a value from a list of conditions. `CASE WHEN` is the same idea. It checks each condition in order and returns the value for the first one that is true, the same as `IFS`. `ELSE` is the final `TRUE` branch.

The example classifies each payment against its invoice due date, for payments made in fiscal 2026. Runs on `cpafirm`.

```sql
WITH timed AS (
    SELECT CASE WHEN p.payment_date <= i.due_date THEN 'On time'
                WHEN DATEDIFF(day, i.due_date, p.payment_date) <= 30 THEN '1 to 30 days late'
                ELSE 'More than 30 days late'
           END AS timing
    FROM payment_applications AS pa
    JOIN payments AS p ON p.payment_id = pa.payment_id
    JOIN invoices AS i ON i.invoice_id = pa.invoice_id
    WHERE p.fiscal_year = 2026
)
SELECT timing, COUNT(*) AS payment_applications
FROM timed
GROUP BY timing
ORDER BY payment_applications DESC;
```

**What it returns.** Of the 4,471 payment applications in fiscal 2026, 1,584 were on time, 2,447 were 1 to 30 days late and 440 were more than 30 days late. Most payments arrive a little after the due date, which is useful to know before you set a collections rule. The `WITH` block is a common table expression. It names the calculated `timing` column once, so the outer query can group by it. Without it, you would repeat the whole `CASE` expression inside the `GROUP BY`. MariaDB runs the same query with one change: `DATEDIFF` takes its dates in the other order, so the second condition reads `DATEDIFF(p.payment_date, i.due_date) <= 30`.

## IFERROR: COALESCE and NULLIF

`IFERROR(B2/C2, 0)` hides a divide-by-zero. SQL Server does not hide it: a divide by zero stops the whole query with an error. Use `NULLIF` to turn a zero denominator into `NULL`. Dividing by `NULL` gives `NULL`, so there is no error. `COALESCE` then turns that `NULL` into the value you want to show.

The next query gives each client's average invoice in fiscal 2026. Runs on `cpafirm`.

```sql
SELECT c.client_name,
       COUNT(i.invoice_id) AS invoices,
       COALESCE(SUM(i.total_amount) / NULLIF(COUNT(i.invoice_id), 0), 0) AS average_invoice
FROM clients AS c
LEFT JOIN invoices AS i
       ON i.client_id = c.client_id
      AND i.fiscal_year = 2026
WHERE c.client_id IN (1, 2, 3, 4)
GROUP BY c.client_name
ORDER BY c.client_name;
```

**What it returns.** Kim, Jennifer averages 475.00 over 2 invoices. Martin, Maria & Edward averages 600.00. Smith, Angela & Anthony has no invoices, and the average shows 0 rather than an error. Wright, Jeffrey averages 1,580.00.

**Use zero with care.** A zero average for a client with no invoices can look like a real amount. When a blank is more honest, keep the `NULL`.

## Text: LEFT, SUBSTRING, TRIM, CONCAT

These functions do what the text formulas do. `LEFT(text, n)` takes the first characters. `SUBSTRING(text, start, length)` is `MID`. `TRIM` removes leading and trailing spaces. `CONCAT` joins pieces of text, the same as `&`. The query below pulls the prefix and year out of each invoice number and builds a label from the client number and name. Runs on `cpafirm`.

```sql
SELECT TOP 5 i.invoice_number,
       LEFT(i.invoice_number, 3) AS prefix,
       SUBSTRING(i.invoice_number, 5, 4) AS invoice_year,
       TRIM(c.client_name) AS client_name,
       CONCAT(c.client_number, ' ', c.client_name) AS label
FROM invoices AS i
JOIN clients AS c ON c.client_id = i.client_id
ORDER BY i.invoice_id DESC;
```

**What it returns.** Five of the newest invoices. Each has the prefix INV, the year 2026 as text, and a label such as C-10207 Morris, Maria & Joseph. The year in the number matches the issue date on all 21,282 invoices. A check like that can catch a mis-keyed invoice, and this one returns zero mismatches.

## Dates: EOMONTH, YEAR, DATEDIFF

In Excel you use `EOMONTH` for month-end, `YEAR` and `MONTH` to split a date, and subtraction for days. The database has the same functions. `EOMONTH` returns the last day of the month for the date it is given. `DATEDIFF(day, start, end)` returns the number of days between two dates.

This query groups the fiscal 2026 receipts by month-end, the same as a pivot grouped by a month column. Runs on `cpafirm`.

```sql
SELECT EOMONTH(payment_date) AS month_end,
       SUM(amount) AS receipts
FROM payments
WHERE fiscal_year = 2026
GROUP BY EOMONTH(payment_date)
ORDER BY month_end;
```

**What it returns.** Twelve rows, one for each month from July 2025 to June 2026. July receipts are 278,746.69 and the heaviest month is April 2026 at 537,834.09. The year total is 3,783,708.16.

MariaDB writes the month-end as `LAST_DAY(payment_date)`. Its `DATEDIFF` takes the two dates in the other order and returns days without a unit: `DATEDIFF(payment_date, due_date)` is the same as the SQL Server `DATEDIFF(day, due_date, payment_date)`.

## Running totals: SUM OVER

Your running total is `=SUM($B$2:B2)`, copied down. The window function `SUM(...) OVER (ORDER BY ...)` does the same with no helper column. The `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` part says "from the first row down to this one". Runs on `cpafirm`.

```sql
WITH monthly AS (
    SELECT EOMONTH(payment_date) AS month_end,
           SUM(amount) AS receipts
    FROM payments
    WHERE fiscal_year = 2026
    GROUP BY EOMONTH(payment_date)
)
SELECT month_end,
       receipts,
       SUM(receipts) OVER (ORDER BY month_end
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM monthly
ORDER BY month_end;
```

**What it returns.** Each month shows its receipts and the total so far. The last row is the year total, 3,783,708.16. The running total is calculated after the grouping, so it works on the monthly figures rather than on the individual payments.

> Diagram: Columns for receipts in July, August and September 2025, with a running total line climbing from 278,746.69 to 489,675.96 to 697,950.67.

## RANK: RANK() OVER

`RANK.EQ` gives each value its position in a list. In SQL, `RANK() OVER (ORDER BY ...)` does the same. The query below ranks clients by fiscal 2026 billing. Runs on `cpafirm`.

```sql
WITH billed AS (
    SELECT c.client_id, c.client_name, SUM(i.total_amount) AS billed_fy2026
    FROM clients AS c
    JOIN invoices AS i ON i.client_id = c.client_id
    WHERE i.fiscal_year = 2026
    GROUP BY c.client_id, c.client_name
)
SELECT TOP 5 RANK() OVER (ORDER BY billed_fy2026 DESC) AS place,
       client_name,
       billed_fy2026
FROM billed
ORDER BY place;
```

**What it returns.** Watson Fitness Corp. first at 65,775.00, then Sammamish Tire & Brake LLC at 61,267.50, The Juniper Shelf Corp. at 44,970.00, Larson Construction Inc. at 40,570.00 and Boise Chiropractic Inc. at 38,712.50.

Ties need a choice. `RANK` gives tied values the same place and then skips the next place. `DENSE_RANK` does not skip. `ROW_NUMBER` gives every row a different number even when values tie.

> Exercise: Which three clients have the most invoices in total? Show the client name and the number of invoices, most first.


## Subtotals: GROUP BY ROLLUP

Excel's Data, Subtotal inserts a subtotal line for each group and a grand total at the bottom. `GROUP BY ROLLUP` does the same in one query. The `GROUPING` function marks the subtotal rows so you can label them. This is the revenue by service line and service, for fiscal 2026. Runs on `cpafirm`.

```sql
SELECT CASE WHEN GROUPING(sl.line_name) = 1 THEN 'All service lines'
            ELSE sl.line_name END AS service_line,
       CASE WHEN GROUPING(sl.line_name) = 1 THEN ''
            WHEN GROUPING(s.service_name) = 1 THEN 'Subtotal'
            ELSE s.service_name END AS service,
       SUM(il.amount) AS revenue
FROM invoice_lines AS il
JOIN invoices AS i       ON i.invoice_id = il.invoice_id
JOIN engagements AS e    ON e.engagement_id = il.engagement_id
JOIN services AS s       ON s.service_id = e.service_id
JOIN service_lines AS sl ON sl.service_line_id = s.service_line_id
WHERE i.fiscal_year = 2026
GROUP BY ROLLUP (sl.line_name, s.service_name)
ORDER BY GROUPING(sl.line_name), sl.line_name, GROUPING(s.service_name), s.service_name;
```

**What it returns.** Each service line, its services and a subtotal line after them. The last row is the grand total, 3,919,677.50, which again matches the invoice total. The `ORDER BY` puts the grand total last and each subtotal after its own detail rows.

MariaDB writes `GROUP BY sl.line_name, s.service_name WITH ROLLUP`. Its subtotal rows show `NULL` in the grouped columns, so you label them in the select list.

## The spreadsheet with a million rows

Excel can hold 1,048,576 rows, and past a certain size the file becomes the problem. It is slow to open, slow to recalculate, and hard to share, and every copy of it becomes a version. A database keeps the table in one place. You ask a question, and the server reads what the question needs and returns one answer.

This query counts every journal line in the general ledger and shows the date range. It returns one row, and no file opens. Runs on `cpafirm`.

```sql
SELECT COUNT(*) AS journal_lines,
       MIN(e.entry_date) AS first_entry,
       MAX(e.entry_date) AS last_entry
FROM gl_journal_lines AS l
JOIN gl_journal_entries AS e ON e.journal_entry_id = l.journal_entry_id;
```

**What it returns.** 93,613 journal lines, from 2021-01-01 to 2026-10-24. The count is small enough to check in Excel, which is the point of the sample. The same query works unchanged when the table holds tens of millions of rows. For a worked example at that size, read [Big data: manufacturer orders](/analyze/big-orders).

## Try it

Run these on `cpafirm`. Each one is a translation of a sheet task you already know.

1. **Filter and count.** How many clients have no invoices at all? Compare this with the 239 clients who had none in fiscal 2026.

```sql
SELECT COUNT(*) AS clients_never_billed
FROM clients AS c
WHERE NOT EXISTS (SELECT 1 FROM invoices AS i WHERE i.client_id = c.client_id);
```

2. **Group and count.** How many invoices were issued in each fiscal year? Sort the years so the newest is first.

```sql
SELECT fiscal_year, COUNT(*) AS invoices
FROM invoices
GROUP BY fiscal_year
ORDER BY fiscal_year DESC;
```

3. **Pivot.** Change the pivot query above so it shows the four quarters of fiscal 2026 for each service line, and then add a column that shows each line's share of the year total. Use `NULLIF` on the year total to protect the division.

For the query building blocks behind all of these, read [SQL essentials](/analyze/sql-essentials). For the close queries that use the same tables, read [SQL for accountants](/analyze/sql-for-accountants).
