Basic · Lesson 5 of 14
COUNT, SUM, AVG
The functions that turn rows into answers.
One number from many rows
Aggregates collapse groups of rows into a single value. COUNT counts rows, SUM adds numbers, AVG averages them, MIN and MAX find extremes.
The NULL subtleties
SUM and AVG ignore NULLs, COUNT(*) counts everything, and COUNT(column) skips NULLs. Choose deliberately, because the choice changes the metric.
Count distinct for unique entities
COUNT(DISTINCT user_id) answers "how many different users", ignoring repeat rows. This is the difference between watch events and watchers.
Examples
Row count
How many events exist in total.
SELECT COUNT(*) AS total_rows FROM watch_events;Sum a measure
Total watch time across all rows.
SELECT SUM(minutes_watched) AS total_minutes FROM watch_events;Distinct users
Unique watchers, not events.
SELECT COUNT(DISTINCT user_id) AS watchers FROM watch_events;