CODEWITHSUNDEEP
sqlsql-serversumcase
Ezerah
Ezerah

Reputation: 49

How to put a sum in a case which is in a sum

I face an issue with the SUM() SQL function.

So here is the code : 

SUM(
CASE WHEN ( CASE 
            WHEN condition 
            THEN SUM(column * VAT)
            END
          ) > 200 
     THEN something
     END)
) AS 'something'

I want to do a conditional sum of a column and use the result of this sum in another sum. But as expected, we can't use an aggregate function (or a sub query) in another one.

Can anyone find a solution without HAVING clause ?

Upvotes: 0

Views: 92

Answers (2)

Stefan Steiger
Stefan Steiger

Reputation: 82166

You could do it without grouping using windowing functions...

SELECT 
    CASE WHEN CONDITION THEN 
    SUM(column * VAT) OVER (Partition by xxxx) 
     ELSE 0.0
     END as sum 
FROM t_whatever

Then you can put it into a CTE, and sum from there...

e.g. 

;WITH CTE AS 
(
    SELECT 
        BP_Year,
        BP_Budget,
        CASE WHEN COUNT(BP_UID) OVER (PARTITION BY BP_Year) > 1 THEN 
            BP_Budget*100.0/NULLIF(SUM(BP_Budget) OVER (PARTITION by BP_Year) ,0.0) 
        ELSE 100
        END AS Per100
    FROM T_UPS_BudgetPositions 
)
SELECT 
     BP_Year
    ,SUM(BP_Budget )
    ,SUM(Per100) 
FROM CTE 

GROUP BY BP_Year

Upvotes: 1

FuzzyTree
FuzzyTree

Reputation: 32392

You can use a derived table that contains the 1st sum.

select sum(case when something > 200 then 1 end)
from (
    select case when condition
    then sum(column * vat)
    end as something
    from mytable
    -- you may want to add a group by here
) t

Upvotes: 0

Related Questions