Please add it all up for me.
Until now, every answer was a list of rows. Today you ask for a summary instead: how many, what is the total, what is the average. You already know these ideas from a café receipt.
The receipt words
A café receipt already ends with how many items you bought and the total. Our receipt adds three more lines at the bottom. Those five lines are today's five new SQL words. Here is the receipt for one of each drink on our menu.
COUNTHow many? Counts the rows.SUMWhat is the total? Adds up a number column.AVGWhat is the average? The total shared out equally: £14.50 ÷ 4 = £3.625, which rounds to £3.63.MINWhat is the smallest? The cheapest, the lowest.MAXWhat is the biggest? The most expensive, the highest.Many rows in, one row out
All five receipt words work the same way. They take all the rows that are left and squash them into one row. Remember this little picture:
WHERE type = 'drink' followed by COUNT counts only the drinks.SELECT item, COUNT(*). COUNT squashes 8 rows into 1, so which item name should that one row show? There is no right answer, so most databases give you an error. (A few let it through and show any item at all, which is even more confusing.) Lesson 4 (GROUP BY) shows how to do it properly.Brackets mean “of”
Each receipt word needs to know what to work on. You put that inside round brackets, straight after the word. When you read it out loud, say the brackets as “of”.
COUNT(*)“the count of every row”The star inside means “rows, whatever is in them”. Gives 8.SUM(price)“the sum of price”Adds every price together. Gives £30.50.AVG(calories)“the average of calories”Total calories ÷ number of rows: 1645 ÷ 8. Gives 205.625.Every piece has a meaning, even the brackets
Please press Next (or click any piece) and watch the sentence grow underneath. Today's new pieces are gold.
Say each one out loud
Swap each word for what you say, and the code becomes a normal sentence again.
| Write | Say it as | Example | Why it's there |
|---|---|---|---|
| The receipt words | |||
| COUNT | “how many” | COUNT(*) | Counts the rows that are left after WHERE. |
| SUM | “the total of” | SUM(price) | Adds up a number column. |
| AVG | “the average of” | AVG(price) | Short for average. The total divided by how many rows. |
| MIN | “the smallest” | MIN(price) | Short for minimum. The lowest value. |
| MAX | “the biggest” | MAX(calories) | Short for maximum. The highest value. |
| Symbols | |||
| ( ) | “of” | SUM(price) | Holds what the word works on. Every ( needs a matching ). Write it straight after the word with no space, because some databases refuse SUM (price). |
| * | “every row” | COUNT(*) | Inside COUNT the star means “count the rows”. Of the five receipt words, only COUNT takes a star. |
| , | “and” | COUNT(*), AVG(price) | Ask for several answers at once. You still get one row, with one column for each. |
| A small bonus | |||
| AS | “call it” | COUNT(*) AS drinks | Gives the answer column a friendly name instead of COUNT(*). |
Playground: watch the rows squash
Please type a query and press Run. Watch the till count each row, then see everything squash into one row.
- 1
- 2
- 3
- 4
- 5
Order of work: FROM, WHERE, then SELECT (where the receipt words squash the rows), then ORDER BY and LIMIT.
Five quick questions
Answering from memory is the fastest way to make something stick. Wrong answers explain themselves, so please don't worry.
Your pocket card
Please take a screenshot of this card. Read it once tomorrow and once next week.
COUNT(*)How many?SUM(price)What is the total?AVG(price)What is the average?MIN(price)What is the smallest?MAX(price)What is the biggest?- Think of the bottom of a café receipt.
- Many rows in, one row out.
- Brackets mean “of”:
AVG(price)= “the average of price”. - SUM and AVG need a number column.
- WHERE runs first, so you can count just the drinks.
ASmeans “call it”:COUNT(*) AS drinks.
SQL Café is a free course by MASSH AI. Want SQL or data training for your team? Please get in touch.