using Sum - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

using Sum

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-04-30 : 04:12:03

hello,
I am trying to use sum like :

select sum(linevalue) from invoicedetail
group by invoiceid
having invoiceid = '1'

and I get a result of -9

the rows for the line value are :

-47.4800
.0000
37.9500
237.4000

it should add up to 227.
why would I get -9 ??

thank you.
J

tinks
Starting Member

34 Posts

Posted - 2004-04-30 : 04:26:39

becareful of potential null values as they will be read as undefined and may lead you to get dodgy answers - but theres nothing i can see thats wrong with your statement ...

try changing your sql select to :

select sum(isnull(linevalue,0)) from invoicedetail
group by invoiceid
having invoiceid = '1'

Taryn-Vee
@>-'-,---

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-04-30 : 04:30:42

thanks for the info Taryn, but I have tried that and it still brings back - 9 !

however, if I do :

select sum(linevalue) from invoicedetail
group by invoiceid, linevalue
having invoiceid = '1'
order by linevalue desc

the -47.4800 value changes to -284.8800 !
why is this happening ?

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-04-30 : 04:35:08

You are getting -9 because linevalues of -47.48 and 37.95 are probably the only invoiceid with a value of 1.

The having clause is what you use to filter based on the result of an aggregation.
The where clause is what you use to filter based on data in the rows.

If you want to sum line value for everything your statement should look like this.

SELECT SUM(LineValue) as LineValue
FROM InvoiceDetail

For the sum of linevalue for each Invoiceid it should look like this
SELECT InvoiceID, SUM(LineValue) as LineValue
FROM Invoicedetail
GROUP BY InvoiceID

For the sum just for the invoiceid of 1 it should look like this:
SELECT InvoiceID, SUM(LineValue) as LineValue
FROM Invoicedetail
WHERE InvoiceID = 1
GROUP BY InvoiceID

For those invoiceid's with a sum value > 100 it should look like this:
SELECT InvoiceID, SUM(LineValue) as LineValue
FROM Invoicedetail
GROUP BY InvoiceID
HAVING SUM(LineValue) > 100

Hope this helps.

Duane.

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-04-30 : 04:51:13

hi Duane, I have tried this but still get -9 !!

I am getting - 9 because if I do sum(linevalue) group by invoiceid, linevalue the minus value changes :
the -47.4800 value changes to -284.8800

this makes no sense to me, ?

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-04-30 : 05:00:50

You should not be grouping by linevalue - it is a field being used in an aggregation.
In fact if you actually get results from this then you are probably not using SQL Server but some other rdms.

I gave you 3 examples, which one returned -9
I'll bet it is this one:

SELECT InvoiceID, SUM(LineValue) as LineValue
FROM Invoicedetail
WHERE InvoiceID = 1
GROUP BY InvoiceID

and that the linevalues for all the rows with an invoiceid of 1 are -47.48 and 37.95.

Try all 3 statements I tried above and let me know what the results are.

Duane.

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-04-30 : 05:27:03

both query 2 and 3 bring back -9.53.
i think I've worked it out,
its me being an idiot, been looking at this for too long now,
there are multiple values for the - value !
sorry, I feel like a time waster !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources