GROUP BY

GROUP BY is where SQL starts getting really powerful. It lets you group rows that have the same value and then run aggregate functions on each group.

Think of it like this: "For each city, give me the average score." Without GROUP BY, you would have to write separate queries for every city. With GROUP BY, you do it in one line.

Basic GROUP BY

-- Average score per city
SELECT city, AVG(score) AS avg_score
FROM students
GROUP BY city;

SQL groups all students from the same city together, then calculates the average score for each group. One row per city. Beautiful.

Multiple Aggregations

SELECT 
  city,
  COUNT(*) AS student_count,
  AVG(score) AS avg_score,
  MAX(score) AS highest_score
FROM students
GROUP BY city;

This gives you a nice summary for each city — how many students, their average, and who topped the class.

The GROUP BY Rule

Here is an important rule: any column in your SELECT that is not inside an aggregate function (like COUNT, AVG) must appear in the GROUP BY clause.

-- This will cause an error in most databases:
SELECT name, city, AVG(score) FROM students GROUP BY city;
-- 'name' is not in GROUP BY and not aggregated

SQLite is actually a bit relaxed about this rule compared to other databases, but do not rely on that. Stick to the rule and you will avoid headaches if you ever switch to MySQL or PostgreSQL.