6 Replies - 1632 Views - Last Post: 28 February 2013 - 04:41 PM Rate Topic: -----

#1 seamadan  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 10-June 12

WHERE syntax error with COUNT

Posted 26 February 2013 - 09:23 PM

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.

Is This A Good Question/Topic? 0
  • +

Replies To: WHERE syntax error with COUNT

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8934
  • View blog
  • Posts: 33,455
  • Joined: 12-June 08

Re: WHERE syntax error with COUNT

Posted 26 February 2013 - 09:24 PM

the key word is "as"..

COUNT(dep_location) as freq

Was This Post Helpful? 0
  • +
  • -

#3 seamadan  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 10-June 12

Re: WHERE syntax error with COUNT

Posted 26 February 2013 - 09:28 PM

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;


Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8934
  • View blog
  • Posts: 33,455
  • Joined: 12-June 08

Re: WHERE syntax error with COUNT

Posted 26 February 2013 - 09:43 PM

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

having COUNT(dep_location) > 1

Was This Post Helpful? 3
  • +
  • -

#5 seamadan  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 10-June 12

Re: WHERE syntax error with COUNT

Posted 26 February 2013 - 09:50 PM

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;


Was This Post Helpful? 0
  • +
  • -

#6 B12-DBA  Icon User is offline

  • New D.I.C Head

Reputation: -2
  • View blog
  • Posts: 5
  • Joined: 22-February 13

Re: WHERE syntax error with COUNT

Posted 28 February 2013 - 04:32 PM

[]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"[/]
Was This Post Helpful? -1
  • +
  • -

#7 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3193
  • View blog
  • Posts: 10,692
  • Joined: 12-December 12

Re: WHERE syntax error with COUNT

Posted 28 February 2013 - 04:41 PM

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.
Was This Post Helpful? 2
  • +
  • -

Page 1 of 1