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  gt  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

User · Answer

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

User · Answer

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

User · Answer

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  gt  1 and B   0            GROUP BY A ID ORDER BY ud DESC            a

User · Answer

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  gt  1 and B   0     GROUP BY A ID      ORDER BY COUNT DISTINCT dNum  DESC

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to exists