proper use of COUNT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

proper use of COUNT

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-01-25 : 09:12:49

I'm needing to find entries in my table where the value for STATENO occurs more than once. i thought the following might provide me that, but it's complaining about "Error 1/25/2012 8:12:17 AM 0:00:00.062 SQL Server Database Error: An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference."

Here is the script:

USE RavalliAccessOnly
GO
SELECT * FROM Temp_EVP_PRP WHERE COUNT(DISTINCT stateno)>1

Where am i wrong?

Kristen
Test

22859 Posts

Posted - 2012-01-25 : 09:17:43

[code]SELECT stateno
FROM Temp_EVP_PRP
GROUP BY stateno
HAVING COUNT(*)>1
[/code]

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-01-25 : 09:18:34

SELECT * FROM Temp_EVP_PRP
group by *
Having COUNT(DISTINCT stateno) > 1

Try above.I have never tried * but alays used the column names in the select and resused the same column names in the group by

Kristen
Test

22859 Posts

Posted - 2012-01-25 : 09:19:10

If you need other columns then


SELECT Col1, Col2, ...
FROM Temp_EVP_PRP 
WHERE stateno IN
(
    SELECT stateno
    FROM Temp_EVP_PRP 
    GROUP BY stateno
    HAVING COUNT(*)>1
)

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-01-25 : 10:43:13

thanks to all

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources