sum(case ....) is coming up with wrong answer...need verification

Question

Hello all,

here's my query:

select name, sum(case when amount<0 then 0 else amount end) as total_amount                    
from table                    
group by name

notes: amounts are either positive or negative

the sum in the above query results in the wrong total, which i can't figure out. when i look at the raw data to validate the results it's not right. Can anyone help without actually seeing the data?

Thank you! :)

Answer 1

0

Refer this to know how you can copy data from table to EXCEL using a query

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

answered Mar 04 '10 at 03:22 AM

Madhivanan
1.1k ● 1 ● 2 ● 6

(comments are locked)

Answer 2

0

I FIGURED IT OUT AND WHAT A DUMMY MOVE ON MY PART!!!

I WAS COPYING AND PASTING THE RESULTS FROM SQL TO EXCEL AND THERFORE AND NOT COPYING THE ENTIRE RESULT SET!

AS A RESULT OF THIS STUPIDITY, THE TOTALS WERE OFF. THE RAW DATA SUMS MATCHED UP TO TOTALS FROM THE CASE STATEMENT.

NOTE TO ALL: DO NOT COPY RESULTS FROM SQL TO EXCEL...YOU MAY MISS DATA!!!

answered Mar 03 '10 at 01:18 PM

katie 2
74 ● 6 ● 6 ● 8

(comments are locked)

Answer 3

0

What do you mean by "wrong?" Can you help us reproduce using a test setup and a subset of the data? Like so:

declare @Table table (name varchar(50), amount int)            
insert into @Table            
    select 'AAAAA', 1 union all            
    select 'AAAAA', -2 union all            
    select 'BBBBB', 2            
            
            
select  name,             
        sum(case when amount < 0 then 0 else amount end) as total_amount             
from    @table             
group            
by      name

answered Mar 02 '10 at 12:56 AM

Nathan Skerl
381 ● 1 ● 1 ● 3

ok...sorry about that. I'll try to post some sample data.

Mar 02 '10 at 01:08 AM katie 1 1

Also, when i say wrong I mean the total for sum(case...) = 642 and the sum in excel=309.

Mar 02 '10 at 01:16 AM katie 1 1

(comments are locked)

sum(case ....) is coming up with wrong answer...need verification

Follow this question

Related Questions