SELECT Orders.BatchNumber AS Remesa
, Products_Joined.ProductCode AS CodigoProducto
, Products_Joined.ProductName AS Producto
, Products_Joined.warehousecustom AS Arancel
, Products_Joined.Vendor_Price AS PrecioUnitario
, OrderDetails.Quantity AS Cantidad
, (OrderDetails.Quantity * Products_Joined.Vendor_Price) AS TotalLinea
FROM ((Products_Joined WITH (NOLOCK) INNER JOIN OrderDetails ON Products_Joined.ProductCode = OrderDetails.ProductCode)
INNER JOIN Orders
ON OrderDetails.OrderID = Orders.OrderID)
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID
WHERE Orders.BatchNumber = 5304
Your SQL is invalid: Invalid column name 'ProductName'.
Select X.Producto,sum(x.Cantidad)
From (SELECT Orders.BatchNumber AS Remesa
, Products_Joined.ProductCode AS CodigoProducto
, Products_Joined.ProductName AS Producto
, Products_Joined.warehousecustom AS Arancel
, Products_Joined.Vendor_Price AS PrecioUnitario
, OrderDetails.Quantity AS Cantidad
, (OrderDetails.Quantity * Products_Joined.Vendor_Price) AS TotalLinea
FROM ((Products_Joined WITH (NOLOCK) INNER JOIN OrderDetails ON Products_Joined.ProductCode = OrderDetails.ProductCode)
INNER JOIN Orders
ON OrderDetails.OrderID = Orders.OrderID)
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID
WHERE Orders.BatchNumber = 5304) as X
Group by x.Producto
... the query results should return only TWO lines, one for Product-A and one for Product-B. Furthermore, the quantity value for each ProductCode should be summed ...GROUP BY produces a distinct set of rows for ALL columns included in that clause.
As you can see, we are pulling 6 fields from the database:
Orders.BatchNumber
Products_Joined.ProductCode
Products_Joined.ProductName
Products_Joined.warehousecustom
Products_Joined.Vendor_Price
OrderDetails.Quantity
SELECT
--Orders.BatchNumber AS Remesa ---not needed
Products_Joined.ProductCode AS CodigoProducto
--, Products_Joined.ProductName AS Producto ----Not Needed
--, Products_Joined.warehousecustom AS Arancel ---- Not Needed
--, Products_Joined.Vendor_Price AS PrecioUnitario ---- Not Needed
, Sum(OrderDetails.Quantity AS Cantidad)
--, (OrderDetails.Quantity * Products_Joined.Vendor_Price) AS TotalLinea
FROM Products_Joined
INNER JOIN OrderDetails ON Products_Joined.ProductCode = OrderDetails.ProductCode
INNER JOIN Orders ON OrderDetails.OrderID = Orders.OrderID
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
WHERE Orders.BatchNumber = 5304
Group by Product_Joined.CodigoProducto
You can build another cartesian then sum with a group by
Open in new window