How to do a SUM inside a case statement in SQL server

Question

I want to add some calculation inside my case statement to dynamically create the contents of a new column but I get the error       Column  Test1 qrank  is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause    This is the code I m working on  case      when test1 TotalType    Average  then Test2 avgscore     when test1 TotalType    PercentOfTot  then  cnt SUM test1 qrank       else cnt end as displayscore   I did try to group but it didn t work   Any hints

User · Answer

The error you posted can happen when you're using a clause in the GROUP BY statement without including it in the select.

Example

This one works!

     SELECT t.device,
            SUM(case when transits.direction = 1 then 1 else 0 end) ,
            SUM(case when transits.direction = 0 then 1 else 0 end) from t1 t 
            where t.device in ('A','B') group by t.device

This one not (omitted t.device from the select)

     SELECT 
            SUM(case when transits.direction = 1 then 1 else 0 end) ,
            SUM(case when transits.direction = 0 then 1 else 0 end) from t1 t 
            where t.device in ('A','B') group by t.device

This will produce your error complaining that I'm grouping for something that is not included in the select

Please, provide all the query to get more support.

User · Answer

If you re using SQL Server 2005 or above  you can use the windowing function SUM   OVER      case  when test1 TotalType    Average  then Test2 avgscore when test1 TotalType    PercentOfTot  then  cnt SUM test1 qrank  over     else cnt end as displayscore   But it ll be better if you show your full query to get context of what you actually need

User · Answer

You could use a Common Table Expression to create the SUM first  join it to the table  and then use the WHEN to to get the value from the CTE or the original table as necessary   WITH PercentageOfTotal  Id  Percentage   AS        SELECT Id   cnt   SUM AreaId   FROM dbo MyTable GROUP BY Id   SELECT      CASE         WHEN o TotalType    Average  THEN r avgscore         WHEN o TotalType    PercentOfTot  THEN pt Percentage         ELSE o cnt     END AS  displayscore  FROM PercentageOfTotal pt     JOIN dbo MyTable t ON pt Id   t Id

[sql] How to do a SUM() inside a case statement in SQL server

Examples related to sql

Examples related to sql-server