How does multilevel grouping work?

Question

I am not sure how the query below is processed. I read this query and i know what output it will give me. But, i wanted to know how it is processed conceptually.

select FirstName, LastName, COUNT(CertificateId) as CertificatesAcheived
from Employee left join [Achivements]
on Employee.EmpID = [Achivements].EmpID
group by FirstName, LastName

Ok, this is how i think it works (tell me if i am wrong) -

First, pairs of the type (First Name, LastName) are generated, ie (f1,n1)(f1,n2)(f2,n5)(f3,n5) etc. Many people can have the same first name and/or last name.

Then, for each such pair, Count(---) will count the number of Certificates achieved.

(...and yes, i know that employees with the exact same name will be considered as one employee, but that is not important for me)

What is the actual question? What do you want to achieve, and what makes you think this query doesn't? — GolezTrol, Dec 29 '12 at 10:16
@skyscraper your explanation of how you think it works seem correct. — Jesper Fyhr Knudsen, Dec 29 '12 at 10:27
@Arkain - do you know any book which explains it like this ? — sky scraper, Dec 29 '12 at 10:29

score 1 · Accepted Answer · answered Dec 29 '12 at 10:23

not sure about sql express 2008 but this is how it generally works:

First a join is created

from Employee left join [Achivements] on Employee.EmpID = [Achivements].EmpID

then groups are created:

group by FirstName, LastName

all rows with same firstname and lastname will fall in same group

the output will be one row for each group: firstname, lastname, and count of rows in the group

select FirstName, LastName, COUNT(CertificateId) as CertificatesAcheived

How does multilevel grouping work?

1 Answers1