Add up and count
INPUT · Slides
01 / 05
"How many records are there?" "How many people left a rating?" — when you look at data, the count is the thing you ask for most.
Counting the returned rows by eye stops being realistic past a hundred. Let the database do the counting too.
02 / 05
COUNT(*) for every rowWrite COUNT(*) and you get how many rows came back. The * means "the row itself", so whatever is in the columns, it counts as one row.
sales has eight. Back it came as a number, no counting required.
SELECT COUNT(*) FROM sales;Result
8
03 / 05
Put a column name in the brackets and it counts only the rows that have a value in that column. Two rows have no rating, so the answer is 6.
Which means the gap between COUNT(*) and COUNT(column) is "how many rows are empty in that column". Knowing which to reach for matters.
SELECT COUNT(*), COUNT(rating) FROM sales;Result
8 | 6
04 / 05
WHERE for "how many match"Put WHERE with it and you can ask "how many rows match this?". This is the one you will use most.
If nothing matches, COUNT answers 0. Not an error — a zero, which is rather a relief.
SELECT COUNT(*) FROM sales WHERE stall = 'Komorebi Studio';Result
3
05 / 05
Write DISTINCT column inside the brackets and it tells you how many kinds of value there are. It is "strip the repeats" from section 1 and "count" put together.
Quicker than listing the stalls and counting them yourself. Let us write some.
SELECT COUNT(DISTINCT stall) FROM sales;Result
3