CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
david sam
david sam

Reputation: 531

Aggregate function error in SQL Server 2008 R2

I want to retrieve the empId that belongs to more than one city. So I use this query:

select empId 
from phone 
group by city 
having  count(city) > 1

but I get an error:

Msg 8120, Level 16, State 1, Line 1
Column 'phone.empId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Upvotes: 0

Views: 159

Answers (3)

Biswabid
Biswabid

Reputation: 1411

You can use ROW_NUMBER() also to work it :
following code should work :

select empId from 
(
select distinct empId,city,ROW_NUMBER() over(partition by empId order by city) rn
 from phone 
 ) a
 where rn>1

Upvotes: 0

jarlh
jarlh

Reputation: 44766

Use GROUP BY and HAVING count distinct city to find empId's with more than 1 city:

SELECT empId
FROM phone
GROUP BY empId
HAVING COUNT(DISTINCT city) > 1

Upvotes: 5

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

what this error is saying is that if you are going to use the GROUP BY clause, so in your SELECT statement you can only "select" the column that you are grouping by and use aggregate functions on that column because the other columns will not appear in the resulting table.

Upvotes: 0

Related Questions