How To Do A Sum() Inside A Case Statement In Sql Server

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

Solution 1:

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(casewhen transits.direction =1then1else0end) ,
            SUM(casewhen transits.direction =0then1else0end) from t1 t 
            where t.device in ('A','B') groupby t.device

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

SELECTSUM(casewhen transits.direction =1then1else0end) ,
            SUM(casewhen transits.direction =0then1else0end) from t1 t 
            where t.device in ('A','B') groupby 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.

Solution 2:

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 GROUPBY Id
)
SELECTCASEWHEN o.TotalType = 'Average' THEN r.avgscoreWHEN o.TotalType = 'PercentOfTot' THEN pt.PercentageELSE o.cnt
    ENDAS [displayscore]
FROM PercentageOfTotal pt
    JOIN dbo.MyTable t ON pt.Id = t.Id

Solution 3:

If you're using SQL Server 2005 or above, you can use the windowing function SUM() OVER ().

casewhen test1.TotalType ='Average'then Test2.avgscore
when test1.TotalType ='PercentOfTot'then (cnt/SUM(test1.qrank) over ())
else cnt
endas displayscore

But it'll be better if you show your full query to get context of what you actually need.