GROUP BY and HAVING

Hello students, welcome to Kwickprep. Can one query give the average marks of every section at once? And why does where avg of marks give an error? Today we will learn group by and having, and solve output questions the way board exams ask them.

All our examples use one table called student. It has five columns: roll, name, section, marks and city. Kabir was absent for the test, so his marks are NULL. NULL means no value at all. It is not zero, and it is not a blank space.

An aggregate like avg gives one answer for the whole table. But a teacher often wants one answer for each section. Group by first puts rows with the same value into one group. Then the aggregate works on each group separately.

Here is the process as a flowchart. First, the rows are collected by section. Section A gets Riya, Neha and Meera. Section B gets Aman and Kabir. Then count star runs on each group. So A gives three and B gives two. The result has one row for each group.

Here is the query. We select the section and count star, and group by section. Section A has three students and section B has two. The groups may come out in any order, so add order by sec if order matters.

Every aggregate works per group. Section A has eighty eight, sixty five and ninety one. The average is two hundred forty four divided by three, which is eighty one point three three. In section B, Kabir's NULL is skipped, so the average is just seventy two.

Remember one important rule. In select, write only the grouped column or aggregate functions. Selecting name while grouping by section is wrong. A group has many names but only one section, so SQL cannot pick one name. Strict MySQL gives an error for it.

To filter groups, we use having, not where. Where filters single rows, while having filters whole groups. Where runs before the rows are grouped, and having runs after grouping. So where cannot use an aggregate, but having can.

Let us find cities with more than one student. Group by city makes three groups. Surat has two, Pune has two, and Delhi has one. Having count star greater than one removes Delhi. Writing where count star greater than one here would give an error.

Both can appear in one query. First, where keeps rows with marks above seventy. That leaves Riya and Meera in A, and Aman in B. Then group by makes the groups, A with two and B with one. Having keeps groups with at least two rows, so only A remains.

Write the clauses in this fixed order. Select and from come first. Where filters the rows. Group by makes the groups. Having filters the groups. Order by sorts the final result, and it always comes last.

Now try questions like the ones in board exams. Pause and predict the output of this query before the next slide. Think group by group. Remember what happens to Kabir's NULL.

Here is the answer, one row per city. Surat has eighty eight and sixty five, so the maximum is eighty eight. Pune has seventy two and ninety one, so ninety one. Delhi has only Kabir, whose marks are NULL. Max of only NULL values is NULL, not zero.

Here is a second question with having. Section A has three marks, adding up to two hundred forty four. Section B has count of marks one, because the NULL is skipped, and the sum is seventy two. Seventy two is not more than one hundred, so section B is removed. Only A appears.

Let us revise what we learned today. Group by puts rows with the same value into one group. The aggregates then give one answer for each group. In select, use only the grouped column or aggregates. Where filters rows before grouping, and having filters groups after grouping. The order is where, group by, having, then order by. Solve output questions group by group, and always check for NULL values.