Totals, averages and counts are the bread and butter of accounting work —
and they're where SQL starts paying for itself. If you can write
=SUM() in Excel, you can write this. Every example below runs against real
cash-receipts data you can query yourself.
Everything here uses the payments and orders tables of the
classicmodels sample database — 273 real payments from 98 customers. Paste any
snippet straight into the SQL Editor or
DBeaver and you'll get the same numbers shown.
Normally a query gives you back one row per record — ask for payments and you get every payment. An aggregate function collapses many rows into a single summarising number.
If you use Excel, you already know this idea. =SUM(B2:B274) takes 273 cells and gives
you one total. SQL's SUM() does exactly the same job — it just works on a column of a
table instead of a range of cells.
Every payment the company has ever received, one row each.
| customerNumber | paymentDate | amount |
|---|---|---|
| 103 | 2004-10-19 | 6066.78 |
| 112 | 2003-06-06 | 1676.14 |
| … | … | … |
273 rows returned — accurate, but you still have to add them up yourself.
The same 273 payments, added up by the database.
| total_cash_received |
|---|
| 8853839.23 |
One row. One number. That is the whole idea of an aggregate.
There are only five aggregate functions worth memorising, and you already know all of them from spreadsheets:
| SQL | Excel | What it does | In accounting terms |
|---|---|---|---|
COUNT(*) | COUNTA | How many rows | How many invoices / payments / transactions |
SUM(x) | SUM | Adds the values | Total cash received, total revenue, account balance |
AVG(x) | AVERAGE | Mean of the values | Average invoice size, average days to pay |
MIN(x) | MIN | Smallest value | Earliest date, smallest payment |
MAX(x) | MAX | Largest value | Latest date, largest exposure |
Three of them at once, over the cash receipts.
| average_payment | smallest_payment | largest_payment |
|---|---|---|
| 32431.653114 | 615.45 | 120166.58 |
Note the ugly precision on the average — we fix that next.
You can ask for several aggregates in one statement, and wrap them in ROUND(x, 2)
to get sensible money values instead of floating-point noise.
The kind of summary you would normally build by hand in Excel.
| payments | total_received | average_payment | smallest | largest |
|---|---|---|---|---|
| 273 | 8853839.23 | 32431.65 | 615.45 | 120166.58 |
The whole cash-receipts picture, from one statement.
An aggregate on its own gives you the grand total. Add GROUP BY and you get
one total per group — the SQL equivalent of a pivot table or a subtotalled report.
Cash received per year — a revenue-by-period report.
| fiscal_year | payments | cash_received |
|---|---|---|
| 2003 | 100 | 3250217.70 |
| 2004 | 136 | 4313328.25 |
| 2005 | 37 | 1290293.28 |
Three groups in, three rows out — one per year.
Group by the customer instead and the same data becomes a receivables summary — who has paid us the most.
Top 10 customers by cash paid.
GROUP BY becomes the
"per" in your question. Total per year. Total per customer. Total per product line.
Anything not aggregated must appear in the GROUP BY.Both filter. The difference is when they run:
WHERE filters individual rows, before they're grouped.
HAVING filters the groups, after the totals are worked out.
That's why you can't say WHERE SUM(amount) > 100000 — at the time
WHERE runs, the sum doesn't exist yet.
Customers who have paid us more than $100,000 in total.
Use them in the same query and each does its own job: WHERE narrows to 2004
payments, then HAVING keeps only the big spenders within that year.
Customers who paid us over $100,000 during 2004 alone.
This one bites accountants in particular, because it silently changes your numbers.
COUNT(*) counts rows.COUNT(shippedDate) counts rows where that column is not NULL.An unshipped order has no ship date — it's NULL — so the two counts disagree. You can turn that into a useful figure:
How many orders exist, how many actually shipped, and the gap.
| all_orders | have_shipped | not_yet_shipped |
|---|---|---|
| 326 | 312 | 14 |
The 14-row gap IS the answer: 14 orders have no ship date.
SUM() and AVG() ignore NULLs
too. An average over a column with blanks divides by the number of non-blank values —
which is usually what you want, but rarely what you expected. If NULL should count as zero,
say so explicitly.273 payments arrived, but not from 273 different customers. DISTINCT counts each
customer once:
Payments received vs. customers who actually paid.
| payments | paying_customers |
|---|---|
| 273 | 98 |
273 payments from 98 customers — so the average customer paid us about 2.8 times.
You can aggregate an expression, not only a plain column. This is how invoice maths works: extend each line (quantity × price), then sum the extensions.
Line count and invoice total for the 10 largest orders.
The same trick, grouped by product line, gives you a revenue breakdown:
Revenue by product line.
SUM(quantityOrdered * priceEach) multiplies
first, row by row, then adds the results — exactly like extending an invoice in a
spreadsheet and totalling the column. SUM(a) * SUM(b) is a different (and almost
always wrong) number.