CODEWITHSUNDEEP
sqlsql-server-2005
C Sharper
C Sharper

Reputation: 8626

max sum of two sums in sql

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'.

Upvotes: 0

Views: 95

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

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

Upvotes: 1

gzaxx
gzaxx

Reputation: 17600

Your case statement is wrong, try this one:

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

Upvotes: 3

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

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

Upvotes: 5

Related Questions