SQL count query

Question

Hi why doesn't this work in SQL Server 2005?

select HALID, count(HALID) as CH from Outages.FaultsInOutages

where CH > 3

group by HALID

I get invalid column name 'CH'

---

i think having was the right way to go but still receive the error: Invalid column name 'CH'.

When running:

select HALID, count(HALID) as CH from Outages.FaultsInOutages group by HALID having CH > 3

Answer 1

You can't use the alias in the where clause or having clause, as it isn't processed until AFTER the result set is generated, the proper syntax is

SELECT HALID, COUNT(HALID) AS CH
FROM Outages.FaultsInOutages
GROUP BY HALID
HAVING COUNT(HALID) > 3

This will group items on HALID, then ONLY return results that have more than 3 entries for the specific HALID