Here is my query, first of all, as it is so far:
Products_Joined.warehousecustom AS Arancel
, Orders.BatchNumber AS Remesa
, Products_Joined.WarehouseBin AS ProductoSimple
, Products_Joined.Vendor_Price AS PrecioUnitario
, SUM(OrderDetails.Quantity) AS Cantidad
, SUM(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
WHERE Orders.BatchNumber = 5392
And here is a sample of the output (see file Sample1.csv):
Now please compare with this second file. I've tweaked the data using a Pivot Table report in Excel to come up with this:
So, in Sample1, there were, for example, 3 different line items called "CUADERNO", \2 with unit prices of $0.57, 15 with unit prices of $1.22 and 6 with unit prices of $1.52. This gives a dollar total of $28.56 for all.
In the Sample2, these three line items have been summarized to just one item: a total of 23 units of "CUADERNO" with a total price (all units) of $28.56.
Individual unit prices are dropped in the Summary Report.
As an FYI: Columns 'A' and 'B' in Summary Report (Sample2.csv) are called "arancel" and "productosimple" These are merely two different labels for the same product. If it's simpler for the purposes of my summary report, I can include just one of those labels (either one) although it would be nice to have both.
Anyway, I hope all that is enough for you to understand what I'm trying to do. Let me know if you need clarification. Thanks!