using the SUM function - 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

using the SUM function

Author

Topic

frani444
Starting Member

4 Posts

Posted - 2007-05-21 : 22:47:13

Hey guys I'm trying to pull out 5 pieces of information out of 2 tables and show the total sales of each item. I have written an sql statement, that almost accomplishes this, but it shows every single sale instead of summing them together and showing the total sales for each product. How do I edit the below statement to archieve this:

SELECT Product.ProductNo, Product.Description, Product.UnitPrice, Sale_Line.Qty, Sale_Line.SalePrice
FROM Sale_Line INNER JOIN
Invoice ON Sale_Line.InvoiceNo = Invoice.InvoiceNo INNER JOIN
Customer ON Invoice.CustomerNo = Customer.CustomerNo INNER JOIN
Product ON Sale_Line.ProductNo = Product.ProductNo

I think i need to use the SUM function but i cant get it too work correctly, any help would be much appreciated!
Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-21 : 22:53:03

[code]
SELECT Product.ProductNo, Product.Description, Product.UnitPrice, sum(Sale_Line.Qty), sum(Sale_Line.SalePrice)
FROM Sale_Line INNER JOIN
Invoice ON Sale_Line.InvoiceNo = Invoice.InvoiceNo INNER JOIN
Customer ON Invoice.CustomerNo = Customer.CustomerNo INNER JOIN
Product ON Sale_Line.ProductNo = Product.ProductNo
GROUP BY Product.ProductNo, Product.Description, Product.UnitPrice
[/code]

frani444
Starting Member

4 Posts

Posted - 2007-05-21 : 23:52:52

hey thanks for that worked like a treat...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources