DISTINCT and Aggregate Functions

~12 min · sql, distinct, aggregate

Level 0Scout

0 XP0/80 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

From rows to summaries

DISTINCT deduplicates row values; aggregates collapse many rows into one number.

COUNT(*) — count rows. COUNT(col) counts non-NULL values of col.
SUM(col), AVG(col), MIN(col), MAX(col) — the obvious ones.
GROUP_CONCAT(col, sep) — concatenate values into a string.
TOTAL(col) — like SUM but returns 0 for empty input (SUM returns NULL).

Tip: SELECT count(*) on a table without a WHERE is O(n) in SQLite — it actually walks the rows. If you call it constantly, cache the count or maintain a counter row. (Postgres has the same property without REINDEX work.)

Code

Aggregates and DISTINCT·sql

SELECT count(*) FROM users;                              -- total rows
SELECT count(DISTINCT role) FROM users;                  -- distinct role count
SELECT role, count(*) FROM users GROUP BY role;          -- grouped count

SELECT min(created_at), max(created_at) FROM messages;

SELECT GROUP_CONCAT(username, ', ') FROM users;
-- alice, bob, carol, dave

External links

Aggregate Functions

Exercise

On the messages table, compute (1) total rows, (2) distinct conversation_ids, (3) per-conversation message counts, (4) earliest and latest created_at per conversation. Use a single SELECT for each and confirm with manual sampling. Note where you used DISTINCT and where GROUP BY was the right call.

DISTINCT and Aggregate Functions

From rows to summaries

Code

External links

Exercise

Progress

Comments 0