sum when there is a quantity field

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

General SQL Server Forums

New to SQL Server Programming

sum when there is a quantity field

Author

Topic

crugerenator
Posting Yak Master

126 Posts

Posted - 2008-07-22 : 17:27:50

I'm trying to sum the total of some dollar amounts.


id  orderID   dollarAmt   quantity
1      2         10           1
2      2         20           2
3      2         100          1

This is what I'm doing:


select    orderID,
          sum (dollarAmt) as total

from      table

where     orderID = 2

group by  orderID

How do I account for the quantity column. I'm getting 130, if I account for the quantity column I should be getting 150. Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-22 : 17:35:29

sum(dollarAmt*quantity) as total

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

crugerenator
Posting Yak Master

126 Posts

Posted - 2008-07-22 : 17:37:28

thanks!

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-07-22 : 23:55:55

You forgotten to take the quality in your calculation of the total amount. Muliplying the quantity by the dollarAmt should do the trick

select orderID,
sum (dollarAmt * quantity) as total
from table
where orderID = 2
group by orderID

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-23 : 13:06:35

EugeneLim11, how does your post provide any additional information?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources