Jimbo Jones Jimbo Jones - 20 days ago 5
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.

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

LEFT JOIN
(
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
ORDER BY Stock.StockID ASC


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

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