Why Does My SQL Subquery Produce an 'Only One Expression Allowed' Error?

SQL Subquery Error: "Only One Expression Allowed"

This error, "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS," arises when a subquery used within an IN clause returns multiple columns. The IN operator expects a single-column result set for comparison.

Problematic Query:

The following query attempts to use a subquery returning A_ID and a count (ud) within an IN clause:

<code class="language-sql">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)</code>

Root Cause:

The inner SELECT statement returns two columns: A_ID and ud. The IN operator cannot handle this; it requires a single column for comparison against the A_ID column in the outer query.

Corrected Query:

The solution is to modify the subquery to return only the A_ID column. The sorting by count can be done within the subquery using ORDER BY COUNT(DISTINCT dNum) DESC:

<code class="language-sql">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)</code>

This revised query correctly selects the top 10% of A_ID values based on the count of distinct dNum values and then uses those A_ID values to filter the outer query.

The above is the detailed content of Why Does My SQL Subquery Produce an 'Only One Expression Allowed' Error?. For more information, please follow other related articles on the PHP Chinese website!