CODEWITHSUNDEEP
sqlms-access
g3blv
g3blv

Reputation: 4367

Conditional SUM with SELECT statement

I like to sum values in a table based on a condition taken from the same table called. The structure of the table as per below. The table is called Data

Data
Type    Value
1   5
1   10
1   15
1   25
1   15
1   20
1   5
2   10
3   5

If the Value of Type 2 is larger than the Value of Type 3 then I like to subtract the Value of Type 2 from the sum of all the Values in the table. I'm not sure how to write the IF statements using Values looked up in the table. I have tried below but it doesn't work.

SELECT SUM(Value)-IF(SELECT Value FROM Data WHERE Type=2>SELECT Value 
FROM Data WHERE Type=3 THEN SELECT Value FROM Data 
WHERE Type=2 ELSE SELECT Value FROM Data WHERE Type=3) FROM Data

or

SELECT SUM(d.Value)-IIF(a.type2>b.type3, a.type2, b.type3) 
FROM Data d, (SELECT Value AS type2 FROM Data WHERE Type=2) a, 
(SELECT Value AS type3 FROM Data WHERE Type=3) b

Upvotes: 0

Views: 6432

Answers (2)

g3blv
g3blv

Reputation: 4367

Thanks for pointing me in the right direction. This is what I came up with in the end. It is a little bit different to the reply above since I'm using MS Access

SELECT SUM(Value)-IIf(SUM(IIf(Type=2, Value, 0)>SUM(IIf(Type=3, Value, 0), SUM(IIf(Type=2, Value, 0), SUM(IIf(Type=3, Value, 0) FROM Data

It is them same as the second suggestion above but adapted to MS Access SQL.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If I follow your logic correctly, then this would seem to do what you want:

select d.value - (case when d2.value > d3.value then d2.value else 0 end)
from data d cross join
     (select value from data where type = 2) d2 cross join
     (select value from data where type = 3) d3 ;

EDIT:

If you want just one number, then use conditional aggregation:

select sum(value) -
        (case when sum(case when type = 2 then value else 0 end) >
                   sum(case when type = 3 then value else 0 end)
              then sum(case when type = 2 then value else 0 end) 
              else 0
         end)
from data;

Upvotes: 3

Related Questions