question

katie 1 1 avatar image
katie 1 1 asked

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

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! :)

t-sqlaggregatescase-statement
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Nathan Skerl avatar image
Nathan Skerl answered

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            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

katie 2 avatar image
katie 2 answered

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!!!

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Madhivanan avatar image
Madhivanan answered

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

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.