max sum of two sums in sql

Question

I have table:

Bonus     Value

500       1400

1500       177

1800       453

1200       100

800       2500

200         50

780        740

I wanted to print the sum of column whichever is maximum.

I Tried Following:

select 
case when sum(bonus)>sum(Value) then sum(bonus) end
case when sum(Value)>sum(bonus) then sum(Value) end
from emp

But i had not got the result.

Error:

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'case'.

but with , it will gives me two columns i want only one column result, just the max sum between two columns — C Sharper
– C Sharper, Commented Jul 4, 2013 at 8:31
case when sum(bonus)>sum(Value) then sum(bonus) when sum(Value)>sum(bonus) then sum(Value) end — chetan
– chetan, Commented Jul 4, 2013 at 8:34

Nenad Zivkovic · Accepted Answer · 2013-07-04 08:31:20Z

5

Your syntax is incorrect, CASE keyword goes only once:

select 
  case when sum(bonus)>sum(Value) then sum(bonus)
     else sum(Value) 
  end as MaxSum
from emp

answered Jul 4, 2013 at 8:31

Nenad Zivkovic

18.6k6 gold badges45 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

C Sharper Over a year ago

@chetan i checked your testing condition. It regardless of high-low, it is returning me whatever is the equal value

Nenad Zivkovic Over a year ago

If they are equal it doesn't matter which one is returned. Sum(value) in this example will be returned. Change > to >= if you want Sum(bonus) for some reason.

C Sharper Over a year ago

@NenadZivkovic I i know it doesnt mattered me. No need to put >= . Thanx

gzaxx · Accepted Answer · 2013-07-04 08:31:32Z

3

Your case statement is wrong, try this one:

select case when sum(bonus)>sum(Value) then sum(bonus) else sum(Value) end
from emp

answered Jul 4, 2013 at 8:31

gzaxx

17.6k2 gold badges40 silver badges55 bronze badges

Comments

ypercubeᵀᴹ · Accepted Answer · 2013-07-04 08:41:11Z

1

Another way:

SELECT TOP (1) *
FROM
  ( SELECT SUM(Bonus) AS MaxSum, 'Bonus' AS SummedColumn FROM emp
    UNION
    SELECT SUM(Value), 'Value' FROM emp
  ) AS tmp
ORDER BY MaxSum DESC ;

Test at SQL-Fiddle

edited Jul 4, 2013 at 8:41

answered Jul 4, 2013 at 8:33

ypercubeᵀᴹ

116k19 gold badges181 silver badges247 bronze badges

1 Comment

ypercubeᵀᴹ Over a year ago

Corrected. I mistakenly thought this was for MySQL at first.

Collectives™ on Stack Overflow

max sum of two sums in sql

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related