Select SUM, Join two tables, group sum by OrderID in SQL Query-CodePudding

Currently trying to solve the reason for this error:

Column 'Orders.OrderID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

My goal is to group all the quantities for the items in each order ID, from the Order Details table

The SQL Server database I'm using is open to the public, as part of W3School's website tools. View it here if you'd like to test your query results:

If you remove the SUM function from SELECT, and remove the last join table statement, you'll see my query aggregates everything correctly up until OrderItems just fine. I have yet to add the OrderRevenue column and order it by descending Revenue value, but big brownie points if you want to go the extra mile and explain how to solve that as well.

CodePudding user response：

Using the example data you provided through w3c:

SELECT o.OrderID, DATEPART(YEAR,o.OrderDate) AS OrderYear, DATEPART(QUARTER,o.OrderDate) AS OrderQuarter,
       E.FirstName   ' '   E.LastName AS EmployeeName, C.CustomerName, S.ShipperName, SUM(d.Quantity) AS Quantity--, SUM(d.Quantity*d.Price) AS OrderRevenue
  FROM Orders o
    INNER JOIN Employees e
      ON o.EmployeeID = e.EmployeeID
    INNER JOIN Customers c
      ON o.CustomerID = c.CustomerID
    INNER JOIN Shippers s
      ON o.ShipperID = s.ShipperID
    INNER JOIN OrderDetails d
      ON o.OrderID = d.OrderID
 GROUP BY o.OrderID, DATEPART(YEAR,o.OrderDate), DATEPART(QUARTER,o.OrderDate),
       E.FirstName   ' '   E.LastName, C.CustomerName, S.ShipperName

OrderID OrderYear   OrderQuarter    EmployeeName        CustomerName            ShipperName     Quantity
--------------------------------------------------------------------------------------------------------
10249   1996        3               Michael Suyama      Tradicao Hipermercados  Speedy Express  49
10250   1996        3               Margaret Peacocl    Hanari Carnes           United Package  60
10368   1996        4               Andrew Fuller       Ernst Handel            United Package  78
10389   1996        4               Margaret Peacock    Bottom-Dollar Marketse  United Package  81
10418   1996        1               Margaret Peacock    QUICK-Stop              Speedy Express  146
10442   1997        1               Janet Leaverling    Ernst Handel            United Package  170

Your syntax error was because you did not define a GROUP BY and reference the columns which you wanted to aggregate to. When you use an aggregate function (like SUM or COUNT) you must also tell the engine which columns you will be grouping by.