CODEWITHSUNDEEP
sql-server
Nate Entleutner
Nate Entleutner

Reputation: 49

How to GROUP BY an alias

I trying to find the amount of classes each of our staff is scheduled to teach, I've counted the amount of times their staffID occurs in the schedule table, and it runs perfect if I only use a staff's first Name but for whatever reason it doesn't work when I try to concatenate their first and last name, and group by the assigned alias. What am I doing wrong?

SELECT DISTINCT sf.StfFirstName  + ' ' + sf.StfLastname As StaffName, COUNT(fc.StaffID) 
FROM Staff sf
JOIN Faculty_Classes fc
ON sf.StaffID = fc.StaffID
join Classes cl
ON fc.ClassID = cl.ClassID
GROUP BY Sf.StaffName

Upvotes: 0

Views: 85

Answers (2)

Simon
Simon

Reputation: 1211

You may repeat the logic as user Used_By_Already suggested or alternatively you can use a common table expression or derived table if you please:

common table expression:

with cte_example -- with name_of_cte as (your query)
as
(
SELECT sf.StfFirstName  + ' ' + sf.StfLastname As StaffName, COUNT(fc.StaffID) 
FROM Staff sf
JOIN Faculty_Classes fc
ON sf.StaffID = fc.StaffID
join Classes cl
ON fc.ClassID = cl.ClassID
)

select *
from cte_example
group by StaffName

derived table:

select *
from
(
SELECT sf.StfFirstName  + ' ' + sf.StfLastname As StaffName, COUNT(fc.StaffID) 
FROM Staff sf
JOIN Faculty_Classes fc
ON sf.StaffID = fc.StaffID
join Classes cl
ON fc.ClassID = cl.ClassID
) t1 --note the table alias
group by StaffName

Upvotes: 0

Paul Maxwell
Paul Maxwell

Reputation: 35573

Just repeat the logic used in the select clause. It may look inefficient but it isn't. Note: Omit the alias given to that logic if pasting into the group by clause.

SELECT sf.StfFirstName  + ' ' + sf.StfLastname As StaffName, 
COUNT(fc.StaffID) 
FROM Staff sf
JOIN Faculty_Classes fc
     ON sf.StaffID = fc.StaffID
join Classes cl
     ON fc.ClassID = cl.ClassID
GROUP BY sf.StfFirstName  + ' ' + sf.StfLastname
;

You can also try without the concatenation, it should also work. 

GROUP BY sf.StfFirstName, sf.StfLastname

Aliases created in the select clause are not reusable by the group by clause because the select clause actually gets performed after the group by, in other words the sequence of writing a sql query is not the sequence of operation.

Upvotes: 3

Related Questions