Why this matters
Real questions usually need more than one table and a summary rather than a list. "Which district had the most mobile money transactions last month?" needs transactions joined to agents, grouped by district and counted. Joins and GROUP BY are what separate a beginner from someone ready for a junior analyst test.
The example tables
Imagine two invented tables for a mobile money operator:
- agents: agent_id, agent_name, district
- transactions: txn_id, agent_id, txn_date, amount
The tables share agent_id.
Aggregation
Aggregate functions summarise many rows into one value: COUNT, SUM, AVG, MIN and MAX.
SELECT COUNT(*) AS txns, SUM(amount) AS total
FROM transactions;GROUP BY
GROUP BY gives one summary row per group:
SELECT agent_id, COUNT(*) AS txns
FROM transactions
GROUP BY agent_id;Safe rule: every column in SELECT must either be in GROUP BY or inside an aggregate function. Some databases, such as SQLite, are less strict, but following the rule keeps your results correct everywhere.