Can you combine case and top statements to label which values are in the top few (Microsoft SQL)

I have a list of fees to summarise, where each fee is used repeatedly over time. My project is to sum the value of each fee over a year, and to label which of these are in the top 80 by value, and which aren’t. I need two columns, one with the fee code, and the other with a ‘yes/no’ value for if the fee is in the top 80 or not.

I’ve tried the following code, which doesn’t work, and I get the error

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

I’m a self-taught programmer, so probably missing something obvious!

Many thanks for your advice on this.

Stuart

select 
    hCODE as 'Fee Code',
    case 
       when hCODE in (select top 80 hCODE, sum(hPRICE) 
                      from dbo.History
                      where hDONE >= dateadd(year,-1,getdate())
                      group by hCODE
                      order by sum(hPRICE) desc) 
          then 'Top 80' 
          else 'Other' 
    end as 'In Top 80'
from 
    dbo.History;

​x
 
select     hCODE as 'Fee Code',    case        when hCODE in (select top 80 hCODE, sum(hPRICE)                       from dbo.History                      where hDONE >= dateadd(year,-1,getdate())                      group by hCODE                      order by sum(hPRICE) desc)           then 'Top 80'           else 'Other'     end as 'In Top 80'from     dbo.History;​

Answer

The problem in this code in the when condition:

when hCODE in (select top 80 hCODE, sum(hPRICE) 
               .....

 
when hCODE in (select top 80 hCODE, sum(hPRICE)                .....​

Your select returns two columns for each row of the result set – but you’re checking to see whether one value (hCODE) is in this set. You cannot return two values from the subquery here – just the one that your hCODE is being compared against.

Advertisement

Answer