x

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

more ▼

asked Mar 02 '10 at 12:46 AM in Default

katie 1 1 gravatar image

katie 1 1
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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
more ▼

answered Mar 02 '10 at 12:56 AM

Nathan Skerl gravatar image

Nathan Skerl
432 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)
10|1200 characters needed characters left

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

more ▼

answered Mar 03 '10 at 01:18 PM

katie 2 gravatar image

katie 2
74 6 6 8

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Mar 04 '10 at 03:22 AM

Madhivanan gravatar image

Madhivanan
1.1k 1 3 6

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x977
x57
x39

asked: Mar 02 '10 at 12:46 AM

Seen: 1523 times

Last Updated: Mar 02 '10 at 10:21 AM