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

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.

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

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

case when test1.TotalType = 'Average' then Test2.avgscorewhen test1.TotalType = 'PercentOfTot' then (cnt/SUM(test1.qrank) over ())else cntend as displayscore

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