Search code examples
sqlsql-serverjoingroup-bysum

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

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: https://www.w3schools.com/sql/trysqlserver.asp?filename=trysql_func_sqlserver_datepart

I appreciate any help, and please let me know if you need more context/information to help me out. Thanks!

PS: please pardon my messy code, new to SQL :)

What I have so far:

SELECT 
    O.OrderID,
    YEAR(O.OrderDate) AS OrderYear,
    DATEPART(qq, O.OrderDate) AS OrderQuarter,
    CONCAT(E.FirstName, SPACE(1), E.LastName) AS EmployeeName,
    C.CustomerName,
    S.ShipperName,
    SUM(D.Quantity) AS OrderItems
FROM
    ((((Orders AS O
JOIN 
    Employees AS E ON O.EmployeeID = E.EmployeeID)
JOIN 
    Customers AS C ON O.CustomerID = C.CustomerID)
JOIN 
    Shippers AS S ON O.ShipperID = S.ShipperID)
JOIN 
    OrderDetails AS D ON O.OrderID = D.OrderID);

What I'm trying to achieve: my mentor gave me a small sample image to reference from. This is how I want to print out the quantities in my query with the column name OrderItems

Reference

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.

Solution

  • 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.