SQL Café · Lesson 3 of 8

Please add it all up for me.

So far: SELECTFROMWHEREORDER BYLIMIT Lesson 1 · Lesson 2

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.

You say to the waiter “Please could you tell me how many drinks are on the menu, what they cost altogether and the average price? Thank you!”
↓ same thing in SQL
SELECT COUNT(*), SUM(price), AVG(price) FROM menu WHERE type = 'drink';
The one thing to remember

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.

SQL CAFÉ
Espresso£2.50
Cappuccino£3.50
Iced Latte£4.50
Hot Chocolate£4.00

ItemsCOUNT4
TotalSUM£14.50
AverageAVG£3.63
CheapestMIN£2.50
Most expensiveMAX£4.50
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.
What they all do

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 still works first. The database throws away rows with WHERE, and only then squashes what is left. So WHERE type = 'drink' followed by COUNT counts only the drinks.
One thing you can't do yet: 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.
The new symbols

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.
Words or numbers? SUM and AVG need a number column (price or calories), because you can't add up words. COUNT, MIN and MAX work on anything: MIN(item) gives the name that comes first from A to Z.
Read the code one word at a time

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 it out loud
Today's words and symbols

Say each one out loud

Swap each word for what you say, and the code becomes a normal sentence again.

WriteSay it asExampleWhy 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 drinksGives the answer column a friendly name instead of COUNT(*).
Your turn

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.

Mission

Ctrl + Enter also runs
Or try one of these
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5

Order of work: FROM, WHERE, then SELECT (where the receipt words squash the rows), then ORDER BY and LIMIT.

Lock it in

Five quick questions

Answering from memory is the fastest way to make something stick. Wrong answers explain themselves, so please don't worry.

Take this with you

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.
  • AS means “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.