CODEWITHSUNDEEP
sqlsql-server
Dev Shah
Dev Shah

Reputation: 3

SQL Server error :only one expression can be specified in the select list when the subquery is not introduced with EXISTS

declare @mode int

select 
    @mode = (select top 1 order_dow, count(*) 
             from orders_denorm 
             group by order_dow 
             order by 2 desc)

I am trying to find the mode of the order_dow column using SQL Server. I get the following error message:

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

However, only the query without using the select @mode works perfectly fine.

Upvotes: 0

Views: 74

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

You want the mode so you only want one column:

select @mode = (select top 1 order_dow from orders_denorm group by order_dow order by count(*) desc);

Perhaps you want both:

select @mode = order_dow, @mode_cnt = cnt
from (select top 1 order_dow, count(*) as cnt
      from orders_denorm
      group by order_dow
      order by count(*) desc
     );

Upvotes: 1

Related Questions