Help with some fancy aggregating (or maybe 'union' clause) in MSSQL to summarize data

Here is my query, first of all, as it is so far:

SELECT
      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
GROUP BY
      Orders.BatchNumber
    , Products_Joined.ProductCode
    , Products_Joined.ProductName
    , Products_Joined.warehousecustom
    , Products_Joined.WarehouseBin 
    , Products_Joined.Vendor_Price
;

Open in new window


And here is a sample of the output (see file Sample1.csv):

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:
sample2.csv

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!
LVL 1
greghollAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
Nothing fancy here... You just have to remove unnecessary lines from your query:
SELECT
      Products_Joined.warehousecustom AS Arancel
    , Products_Joined.WarehouseBin    AS ProductoSimple 
    , SUM(OrderDetails.Quantity)      AS Sum_of_Cantidad
    , SUM(OrderDetails.Quantity * Products_Joined.Vendor_Price) AS Sum_of_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
GROUP BY
      Products_Joined.WarehouseBin
    , Products_Joined.warehousecustom
;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
greghollAuthor Commented:
Oh. Man, you rock. Way more simpler than I imagined. thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.