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'

Answer 1

Try

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

Your query has two errors:

• Using where an aggregate when grouping by, solved by using having
• Using an alias for an aggregate in the condition, not supported, solved by using the aggregate again

Answer 2

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 3

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

Answer 4

Worked great thanks a lot to both of you.