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)

Answer 1

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