Jimbo Jones Jimbo Jones - 1 year ago 67
SQL Question

How to us Group By in SQL with a JOIN

I am wondering if anyone could help me. I trying to write a query which will group all the order detail lines to each product.

Line.NetAmount, Line.QtyDespatch, Line.QtyOrder,
Line.Price, Line.Price * Line.QtySent AS 'Value'
FROM dbo.orderdetails line

SELECT products.ProductID, products.CompanyID AS StockCompanyID,
products.StockCode FROM dbo.products
Stock ON Line.ProductID = products.ProductID AND products.StockCompanyID = Line.CompanyID

WHERE Line.CompanyID = 1

The results I am getting are for each order details line individually but I want to group-by the each product id and have (Line.Price * Line.QtySent) as sum for each product and show each product once.

The result I am getting are

PID Net sent qty Price Value
39044 12 0 5 2.4 0
39044 12 0 5 2.4 0
39044 12 0 5 2.4 0
39044 12 0 5 2.4 0

But I do not want to get it for each line but a cumulative value for all line by product. Basically cumulative totals for each product

Answer Source

You would need to group by the ProductId and then sum the Line.Price multiplied by the Line.QtySent. You would want something like this:

 SELECT p.ProductID, SUM(Line.Price * Line.QtySent) AS 'Value'
 FROM  dbo.orderdetails line   
 LEFT JOIN dbo.products p ON 
 Line.ProductID = p.ProductID AND p.CompanyID = Line.CompanyID       
 WHERE Line.CompanyID = 1
 GROUP BY p.ProductID 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download