Reg: Add SUM of two values vs. Add values and SUM it gives different output

Asked Mar 02 '16 at 11:15

Active Mar 02 '16 at 11:20

Viewed 50 times

This might be a silly question. I have a staff table which stores staff Information. I need to get the salary for each staff. So I tried below queries

SELECT staffid, SUM(ISNULL(amount,0) +ISNULL(bonus,0)) as amount
from payments
group by staffid

But I got wrong output. Then I tried as below,

SELECT staffid, SUM(ISNULL(amount,0)) + SUM(ISNULL(bonus,0))
from payments
group by staffid

This time I got the correct output. But I wonder why the difference occurs. Does above two sum operations are different? someone please give some idea about this.

edited Mar 02 '16 at 11:20

asked Mar 02 '16 at 11:15

bmsqldev

2,627
10
31
65

Are you sure that it is SQL Server and not mySQL? **`The isnull function requires 2 argument(s).`** – Lukasz Szozda Mar 02 '16 at 11:17
yes. sql-server 2012. Sorry I created this query for demonstration . now i have corrected the query – bmsqldev Mar 02 '16 at 11:18
1

Are amount and bonus `FLOAT/REAL`? – Lukasz Szozda Mar 02 '16 at 11:22
@lad2025 they are money datatype – bmsqldev Mar 02 '16 at 11:25
@jarlh i dont get your point, I have handled ISNULL in both statements – bmsqldev Mar 02 '16 at 11:26
what means *'I got wrong output'* ? – dani herrera Mar 02 '16 at 11:26
@jarlh I wanted to exlude this case http://stackoverflow.com/questions/33322778/avg-of-float-inconsistency/33323021#33323021 The datatype matters. – Lukasz Szozda Mar 02 '16 at 11:27
@bmsqldev, sorry, my bad, multi-tasking error. – jarlh Mar 02 '16 at 11:28
I meant I should get the correct salary amount for each user (amount + bonus). which I didn't get in first case – bmsqldev Mar 02 '16 at 11:28
2

@bmsqldev Please prepare sample data to illustrate your case http://sqlfiddle.com – Lukasz Szozda Mar 02 '16 at 11:46
good idea, I cannot reproduce this bug – Ruslan K. Mar 02 '16 at 11:51
it should be related to rounding. for #1, rounding executes after add up salary & bonus, #2 rounds result after the total amt of 12 months is calculated. – RAY Mar 02 '16 at 15:09

Reg: Add SUM of two values vs. Add values and SUM it gives different output

0 Answers0