GROUP BY and HAVING without the confusion

GROUP BY folds many rows into one row per group. Aggregate functions such as count, sum, avg, min and max then summarise each group.

The order SQL works in

It helps to read a grouped query in the order the database evaluates it:

1. FROM picks the table.
2. WHERE removes individual rows.
3. GROUP BY makes the groups.
4. HAVING removes whole groups.
5. SELECT computes the output columns, then ORDER BY sorts them.

So WHERE filters rows before grouping and HAVING filters groups after grouping. You cannot use avg(...) in WHERE, because no groups exist yet at that point.

CREATE TABLE marks (name text, branch text, score int);
INSERT INTO marks VALUES
  ('A', 'CSE', 82), ('B', 'CSE', 74), ('C', 'ECE', 68), ('D', 'ECE', 59), ('E', 'MECH', 88);
SELECT branch, count(*) AS students, round(avg(score), 1) AS avg_score
FROM marks
WHERE score >= 60
GROUP BY branch
HAVING count(*) >= 1
ORDER BY avg_score DESC;

MECH|1|88.0
CSE|2|78.0
ECE|1|68.0

D (59) was removed by WHERE before the ECE average was taken.

The one rule about SELECT

In a grouped query, every column in SELECT must either be in GROUP BY or be inside an aggregate. Selecting name here would be an error, because one group can hold many names.

Interview favourite: departments above the overall average

CREATE TABLE emp (name text, dept text, salary int);
INSERT INTO emp VALUES ('A','Eng',100),('B','Eng',80),('C','Sales',50),('D','Sales',60),('E','HR',40);
SELECT dept FROM emp GROUP BY dept
HAVING avg(salary) > (SELECT avg(salary) FROM emp)
ORDER BY dept;

Eng

The overall average is 66, and only Eng (90) is above it. Try more like this in UniCircle interview prep: databases and SQL.