We help IT Professionals succeed at work.
Get Started

Help with a MSSQL statement Select Query (Grouping)

125 Views
Last Modified: 2015-08-25
Hi. I'm doing a Query, and I would like to group the result by the value "Products_Joined.ProductCode"

Here is my code so far:

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

Open in new window


So for example, if there are several lines of the code "Product-A" and several lines of "Product-B", 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, i.e.: OrderDetails.Quantity.

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

Please help out with that. Thanks. Let me know if it all makes sense.
Comment
Watch Question
EE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
This problem has been solved!
Unlock 2 Answers and 15 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE