[seamadan]

This is certainly a dumb question, so be gentle... I'm trying to return a count of the location column where only locations with more than 1 are displayed

This query

SELECT dep_location,
	COUNT(dep_location) freq
FROM ISQA.dbo.department
WHERE freq > 1
GROUP BY dep_location;

receives this error: Msg 207, Level 16, State 1, Line 4
Invalid column name 'freq'.

It has to be simple, but I haven't found the answer. Thanks in advance for the help.

[modi123_1]

the key word is "as"..

COUNT(dep_location) as freq

[seamadan]

I'm afraid I am still getting the error with this:

SELECT dep_location,
	COUNT(dep_location) as freq
FROM ISQA.dbo.department
WHERE freq > 1
GROUP BY dep_location;

[modi123_1]

get rid of line 4 and use a "having".

having COUNT(dep_location) > 1

[seamadan]

Bingo, that did it- thanks for the help!

Final result if anyone ever cares:

SELECT dep_location,
COUNT(dep_location) as freq
FROM ISQA.dbo.department
GROUP BY dep_location
HAVING COUNT(dep_location) > 1;

[B12-DBA]

[]You have used "freq" as it is a column of the specified table "ISQA.dbo.department" in the database. But this is used as a label for "dep_location" attribute, so please try to use the "WHERE count(dep_location) > 1" instead of "WHERE freq > 1"[/]

[andrewsw]

HAVING is used to check conditions after the aggregation takes place, WHERE is used before the aggregation takes place. So:

HAVING COUNT(dep_location) > 1;

is correct.