[sql] Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

My query is as follows, and contains a subquery within it:

 select count(distinct dNum)
 from myDB.dbo.AQ
 where A_ID in 
  (SELECT DISTINCT TOP (0.1) PERCENT A_ID, 
            COUNT(DISTINCT dNum) AS ud 
 FROM         myDB.dbo.AQ
 WHERE     M > 1 and B = 0 
 GROUP BY A_ID ORDER BY ud DESC)

The error I am receiving is ...

Only one expression can be specified in the select list when the subquery is not
introduced with EXISTS.`

When I run the sub-query alone, it returns just fine, so I am assuming there is some issue with the main query?

You should return only one column and one row in the where query where you assign the returned value to a variable. Example:

select * from table1 where Date in (select * from Dates) -- Wrong
select * from table1 where Date in (select Column1,Column2 from Dates) -- Wrong
select * from table1 where Date in (select Column1 from Dates) -- OK

It's complaining about

COUNT(DISTINCT dNum) AS ud 

inside the subquery. Only one column can be returned from the subquery unless you are performing an exists query. I'm not sure why you want to do a count on the same column twice, superficially it looks redundant to what you are doing. The subquery here is only a filter it is not the same as a join. i.e. you use it to restrict data, not to specify what columns to get back.

Just in case it helps someone, here's what caused this error for me: I needed a procedure to return json but I left out the for json path:

set @jsonout = (SELECT ID, SumLev, Census_GEOID, AreaName, Worksite 
            from CS_GEO G (nolock) 
                join @allids a on g.ID = a.[value] 
            where g.Worksite = @worksite)

When I tried to save the stored procedure, it threw the error. I fixed it by adding for json path to the code at the end of the procedure:

set @jsonout = (SELECT ID, SumLev, Census_GEOID, AreaName, Worksite 
            from CS_GEO G (nolock) 
                join @allids a on g.ID = a.[value] 
            where g.Worksite = @worksite for json path)

Apart from very good responses here, you could try this as well if you want to use your sub query as is.

Approach:

1) Select the desired column (Only 1) from your sub query

2) Use where to map the column name

Code:

 SELECT count(distinct dNum)
 FROM myDB.dbo.AQ
 WHERE A_ID in 
   (
    SELECT A_ID 
    FROM (SELECT DISTINCT TOP (0.1) PERCENT A_ID, COUNT(DISTINCT dNum) AS ud 
          FROM         myDB.dbo.AQ
          WHERE     M > 1 and B = 0 
          GROUP BY A_ID ORDER BY ud DESC
         ) a 
   )