Selecting data based on 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

Selecting data based on count

Author

Topic

satheesh
Posting Yak Master

152 Posts

Posted - 2011-03-31 : 04:50:10

Hi All,

I want to reterview data in a table, where emailaddress is entered more than one.

ie.
select cstid,firstname,emailaddress from Customer
group by EmailAddress
having COUNT (emailaddress) > = 2

I got this error:-

Column customer.cstid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

How to change the query.I need to select some fields where emailaddress entered more than once

Regards,
SG

ishchopra
Starting Member

24 Posts

Posted - 2011-03-31 : 05:08:17

Try that

select cstid,firstname,emailaddress from Customer
group by EmailAddress,cstid,firstname
having COUNT (emailaddress) > = 2

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-03-31 : 05:10:13

WITH CTE_Table
AS ( SELECT cstid,
firstname,
emailaddress,
COUNT(EmailAddress) OVER ( PARTITION BY EmailAddress ) AS CountEmailAddress
FROM Customer
)
SELECT *
FROM CTE_Table
WHERE CountEmailAddress > 1

SOURCE: http://connectsql.blogspot.com/2011/03/sql-server-magical-over-clause.html?utm_source=BP_recent
--------------------------
http://connectsql.blogspot.com/

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-03-31 : 06:05:20

add cstid,firstname in the group by clause...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources