AI Hints
Homeโ€บSQL Tutorialโ€บCounting and Totals
SQL ยท Lesson 31 of 126

Counting Rows

COUNT.

โฑ 2 min readโ–ถ Video at 43:54๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

Up to now, every query has returned rows. Lots of rows. But Helen's questions are often about one number. How many customers do I have? How much stock is there? To get one number from many rows, we use aggregate functions. And the first one is count.

Counting Rows
From the video: Counting Rows
  • Many rows in, one number out
  • How many customers?
  • Meet aggregate functions

How many?

Count star counts every row in the table. Instead of five hundred rows, we get back one row, with one number. Run it. Helen has five hundred customers.

query.sqlsql
SELECT COUNT(*)
FROM customers;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ COUNT(*) โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 500      โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Count a column

Now a subtle but important detail. Count star counts rows. But count of a column counts only the rows where that column is not null. So count of email tells us how many customers gave an email. Run it. Five hundred customers, four hundred and sixty nine with an email, and four hundred and eighty five with a city.

query.sqlsql
SELECT COUNT(*)     AS customers,
       COUNT(email) AS with_email,
       COUNT(city)  AS with_city
FROM customers;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ customers โ”‚ with_email โ”‚ with_city โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 500       โ”‚ 469        โ”‚ 485       โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Count different values

Put distinct inside count, and you count different values instead of rows. How many different countries and cities do our customers live in? Run it. Six countries, and twelve cities.

query.sqlsql
SELECT COUNT(DISTINCT country) AS countries,
       COUNT(DISTINCT city)    AS cities
FROM customers;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ countries โ”‚ cities โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 6         โ”‚ 12     โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Summary

Count star counts rows, count of a column skips nulls, and count distinct counts different values. Next, adding things up.

Key takeaways
  • COUNT(*) counts rows
  • COUNT(col) skips NULLs
  • COUNT(DISTINCT col)
Next up: Adding Up with SUM and AVG โ€” totals and averages