CODEWITHSUNDEEP
sql
gpark
gpark

Reputation: 3

Using case statement on sum aggregate

I'm trying to run a case on an aggregate sum but can't seem to get this working...essentially I want to return 1 if the sum of the column is > 0...

SELECT Shop.Brands, Shop.Brand, Shop.T3, Shop.ShopName, Shop.Period 
CASE WHEN sum(PLData.Actuals) > 0 THEN 1 ELSE 0 END as Actuals,
CASE when sum(PLData.Budgets) > 0 THEN 1 ELSE 0 END as Budget,
CASE when sum(pldata.ForecastLedger) > 0 THEN 1 ELSE 0 END as Forecast
FROM SunFinance.dbo.Shop Shop LEFT OUTER JOIN SunFinance.dbo.PLData PLData ON Shop.T3 = PLData.Shop
WHERE Shop.BusinessType In ('CORPORATE','RETAIL','WHOLESALE')
AND PLData.Account Like '488%')
GROUP by shop.brand, shop.brands, shop.t3, shop.shopname, Shop.Period

Where am I going wrong?

Upvotes: 0

Views: 64

Answers (1)

N West
N West

Reputation: 6819

If your DBMS doesn't let you use aggregates in case expressions, you can try to do the aggregation first in an inline view, then do your CASE expressions.

WITH RESULTS AS (
SELECT Shop.Brands
     , Shop.Brand
     , Shop.T3
     , Shop.ShopName
     , Shop.Period 
     , sum(PLData.Actuals) as Actuals
     , sum(PLData.Budgets) as Budget,
     , sum(pldata.ForecastLedger) as Forecast
FROM SunFinance.dbo.Shop Shop 
LEFT OUTER JOIN SunFinance.dbo.PLData PLData 
  ON Shop.T3 = PLData.Shop
WHERE Shop.BusinessType In ('CORPORATE','RETAIL','WHOLESALE')
      AND PLData.Account Like '488%'
GROUP by shop.brand, shop.brands, shop.t3, shop.shopname, Shop.Period
)
SELECT brands, brand, t3, shopname, period,
       case when actuals > 0 then 1 else 0 end as actuals, 
       case when budget > 0 then 1 else 0 end as budget, 
       case when forecast > 0 then 1 else 0 end as forecast
  FROM results

Upvotes: 1

Related Questions