Sum Statement - 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

General SQL Server Forums

New to SQL Server Programming

Sum Statement

Author

Topic

saltzmanjoelh
Starting Member

10 Posts

Posted - 2007-10-17 : 19:33:20

I have a SalesOrder table and a related SalesOrderLineItems table.

I am trying to multiply UNITPRICE by QTY then SUM that up for a GRANDTOTAL.

Would it look like this?

SELECT Sum(Sales_Orders_Line_Items.qty) AS GrandTotal, Sales_Orders_Line_Items.sales_order_id
FROM Sales_Orders_Line_Items
WHERE (((Sales_Orders_Line_Items.sales_order_id)=1154));

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-17 : 19:46:10

I don't see where you are multiplying.

SELECT SUM(a*b) AS GrandTotal
FROM table1
...

You won't be able to get sales_order_id in your result set with this query though. Show us sample data though and the expected result set so that we can come up with an actual working query for you.

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

saltzmanjoelh
Starting Member

10 Posts

Posted - 2007-10-17 : 20:34:34

OOPs

SELECT (Sum(Sales_Orders_Line_Items.qty)*UNITPRICE) AS GrandTotal, Sales_Orders_Line_Items.sales_order_id
FROM Sales_Orders_Line_Items
WHERE (((Sales_Orders_Line_Items.sales_order_id)=1154));

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-17 : 23:43:23

You'll get an error if you try to run that. Like I mentioned in my last post, please provide sample data and expected result set so that we can help you better.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-18 : 01:35:03

I don't think you can multiply like this. What happens if different items has other unitprice?
You also need a GROUP BY.

SELECT		Sum(qty * UNITPRICE) AS GrandTotal,
		sales_order_id
FROM		Sales_Orders_Line_Items
WHERE		sales_order_id = 1154
GROUP BY	sales_order_id

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources