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?

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.

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

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)

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)

For projection in subquery, you can use

SELECT t.col1,t.col2
FROM table1 t
WHERE EXISTS (SELECT st.col1,st.col2
FROM table2 st
WHERE st.fcol = t.fcol)