How does Group by and Having works

Question

I am new to SQL and after writing some queries I wanted to understand how SQL "internally" processes the queries. I take one query from another post in stackoverflow:

select name from contacts
group by name
having count(*) > 1

My question is: group by name merges all rows with the same name into one row, how does then count know how many rows with the same name were merged. I am trying to split all steps in the processing of the query in order to understand how it is exactly working, but in this case it seems like you cannot split it. Thanks in advance.

Answer 1

From your sql query that you show there the execution sequence will be like this show below

from contacts

knowing which tables's data you are getting, next will be your WHERE clause but in this case you don't have one so will follow to the next step which is

group by name

group all the same name to a row of record.

side note: Now the SELECT statement still haven run yet, therefore when HAVING statement run can count the row that the same name has

Next is your

having count(*) > 1

filter up all the record which count more than 1, and lastly will be the SELECT

select name

above was the execute sequence for your example shown.

And these is the full sequence of sql query

1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

Hope it help ya.