this Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >=

Question

i wrote a query to get total count of devices for user. but when i run the query for single user it working fine. when i keep mutiple emailids its not working getting message subquery should not contain multiple. I unable to change below query to accept multiple values. Can anyone help on this.

select distinct UserDisplayName,[UserName/Mail],SerialNumber,LastSeen as Lastcheckin,Model, EnrollmentStatus,(select count(*) from Intunedevices where [UserName/Mail] in 
('aaa-xyz.onsite-caller@xyz.com','gbidi.i.habab@gsk.com') group by UserDisplayName) as EMSCOUNT 
 from Intunedevices where [UserName/Mail] in ('aaa-xyz.onsite-caller@xyz.com',
'gbidi.i.habab@xyz.com')

Why are you using SQL Server 2008, which is no longer supported (and hence dangerous to use because it doesn't get security updates)? — Gordon Linoff, Aug 12 '21 at 19:18
The error is telling you the problem here, what about the error don't you understand? If you run the query on its own you get multiple rows, and thus the error because that isn't allowed. — Thom A, Aug 12 '21 at 19:24

score 0 · Answer 1 · answered Aug 12 '21 at 19:24

The problem is the group by in your sub-query is returning multiple rows. It should probably be correlated - I'm not completely sure as the in criteria are slightly different, is that a typo in your sample data?

Try the following:

select distinct 
    UserDisplayName, 
    [UserName/Mail], 
    SerialNumber, 
    LastSeen as Lastcheckin,Model, 
    EnrollmentStatus,
    (select count(*) from Intunedevices d where d.[UserName/Mail] = id.[UserName/Mail] and d.UserDisplayName = id.UserDisplayName) as EMSCOUNT 
 from Intunedevices id
 where id.[UserName/Mail] in ('aaa-xyz.onsite-caller@xyz.com','gbidi.i.habab@xyz.com')

score 0 · Answer 2 · answered Aug 12 '21 at 19:29

Instead of using DISTINCT, just use a GROUP BY in your outer query, and then you can aggregate any non-grouped columns, eg:

select UserDisplayName,
       [UserName/Mail],
       SerialNumber,
       LastSeen as Lastcheckin,
       Model, 
       EnrollmentStatus,
       count(*)  EMSCOUNT 
from Intunedevices 
where [UserName/Mail] in ('aaa-xyz.onsite-caller@xyz.com', 'gbidi.i.habab@xyz.com')
group by UserDisplayName,
         [UserName/Mail],
         SerialNumber,
         LastSeen,
         Model, 
         EnrollmentStatus

this Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >=

2 Answers2