T SQL - Group By/Having Question

If i run the code below then i'll get the error returned - can anyone suggest the easiest way around this...

Thanks


Msg 116, Level 16, State 1, Line 4
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Select * from 
[users]
where email in 
(
	SELECT email, COUNT(email) AS NumOccurrences, companyid
	FROM [users]
	GROUP BY email, companyid
	HAVING (COUNT(email) > 1 )     
)

Open in new window

paulCardiffAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Ashish PatelConnect With a Mentor Commented:
Try this.

Select * from
[users]
where email in
(
      SELECT email  FROM [users]
      GROUP BY email, companyid
      HAVING (COUNT(email) > 1 )    
)
0
 
jourikCommented:
For clarification:

When stating a subquery to define a column criteria, it has to output one column only.
Otherwise, the parent query won't know what column to pick.
0
All Courses

From novice to tech pro — start learning today.