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

Question

You can't return two (or multiple) columns in your subquery to do the comparison in the WHERE A_ID IN (subquery) clause - which column is it supposed to compare A_ID to? Your subquery must only return the one column needed for the comparison to the column on the other side of the IN. So the query needs to be of the form:

SELECT * From ThisTable WHERE ThisColumn IN (SELECT ThatColumn FROM ThatTable)

You also want to add sorting so you can select just from the top rows, but you don't need to return the COUNT as a column in order to do your sort; sorting in the ORDER clause is independent of the columns returned by the query.

Try something like this:

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

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.

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 
   )

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)