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

Question

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 weird it states 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?

Answer 1

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.

Answer 2

You can't return two columns in your subquery to do the comparison in the WHERE clause. However, you also don't need to return the COUNT as a column in order to do your sort.

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)

Answer 3

The reason for the error is:

 where A_ID in (SELECT DISTINCT TOP (0.1) PERCENT A_ID, 
                       COUNT(DISTINCT dNum) AS ud

The subquery is returning two columns, but the IN comparison is only for one column. In order for it to work, the subquery can only return one column that matches by data type of the column being compared.

Here's how I rewrote your query:

SELECT COUNT(DISTINCT z.dNum)
  FROM myDB.dbo.AQ z
  JOIN (SELECT DISTINCT TOP 10 PERCENT x.a_id
          FROM (SELECT t.a_id,
                       COUNT(DISTINCT t.dnum) 'ud'
                  FROM MYDB.dbo.AQ t
                 WHERE t.m > 1 
                  AND t.b = 0 
             GROUP BY t.a_id 
             ORDER BY ud DESC) x) y ON y.a_id = z.a_id