Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2009
    Posts
    50

    Unanswered: Learning with Northwind and stuck with Aggregate and Group By

    Hi All,

    Installed the Northwind database for data to practice with. I'm trying to combine data from several tables to make the orders table more readable. Meaning, I'm trying to replace the EmployeeID field with the combination of the firstname and lastname fields from the Employees table. Everything works fine until I try to sum the Unit price field from the [Order Details] table. Using just a SUM() function or the Select statement below causes the error and any combination of fields in the Group By command don't correct it. It's clear that I'm doing something wrong, I'm just not sure how to get the data I want or use the group by command properly. Query below:

    Select o.OrderID, c.companyName, e.firstname + ' ' + e.lastname EmployeeName, o.orderdate, s.companyName,
    o.Freight, o.shipName, o.ShipAddress, (Select Sum(od.UnitPrice) from [Order Details] od where od.OrderID = o.OrderID)as Amount
    from orders o, customers c, Employees e, Shippers s, [Order Details] od
    where o.CustomerID = c.CustomerID
    and o.EmployeeID = e.EmployeeID
    and o.ShipVia = s.ShipperID
    and o.orderid = od.orderid
    group by o.orderid


    Select o.OrderID, c.companyName, e.firstname + ' ' + e.lastname EmployeeName, o.orderdate, s.companyName,
    o.Freight, o.shipName, o.ShipAddress, Sum(od.UnitPrice) as Amount
    from orders o, customers c, Employees e, Shippers s, [Order Details] od
    where o.CustomerID = c.CustomerID
    and o.EmployeeID = e.EmployeeID
    and o.ShipVia = s.ShipperID
    and o.orderid = od.orderid
    group by o.orderid

    EDIT:
    Running the first query (with the select statement) works, but returns a row for each of the the items that was ordered for that OrderID and NOT using the Group By. I would like to have the SUM() of the items ordered in one row. Is this possible?
    Last edited by jbedson; 01-16-14 at 11:12.

  2. #2
    Join Date
    Dec 2009
    Posts
    50
    Got the answer.

    Using DISTINCT

    Select DISTINCT o.OrderID, c.companyName, e.firstname + ' ' + e.lastname EmployeeName, o.orderdate, s.companyName,
    o.Freight, o.shipName, o.ShipAddress, (Select Sum(od.UnitPrice) from [Order Details] od where od.OrderID = o.OrderID)as Amount
    from orders o, customers c, Employees e, Shippers s, [Order Details] od
    where o.CustomerID = c.CustomerID
    and o.EmployeeID = e.EmployeeID
    and o.ShipVia = s.ShipperID
    and o.orderid = od.orderid

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    DISTINCT is not the answer here.

    When using aggregate functions [Min(), Max(), Sum(), Count(), Avg(), etc] you must GROUP BY all other columns in your statement that are not being aggregated.
    George
    Home | Blog

  4. #4
    Join Date
    Dec 2009
    Posts
    50
    Thanks for the reply.

    Question though (for understanding and not for trying to be an ass):
    can you tell me why?
    I redid the query with the GROUP BY set up with all of the non aggregate fields and got the same results. So my guess is that for more complicated cases the DISTINCT wont work and I will need to use the GROUP BY instead. I'm trying to understand why it works in this case or why I should use the GROUP BY. My query below:

    This works and gives me 830 rows:
    Select DISTINCT o.OrderID, c.companyName, e.firstname + ' ' + e.lastname EmployeeName, o.orderdate, s.companyName,
    o.Freight, o.shipName, o.ShipAddress, (Select Sum(od.UnitPrice) from [Order Details] od where od.OrderID = o.OrderID)as Amount
    from orders o, customers c, Employees e, Shippers s, [Order Details] od
    where o.CustomerID = c.CustomerID
    and o.EmployeeID = e.EmployeeID
    and o.ShipVia = s.ShipperID
    and o.orderid = od.orderid
    order by o.OrderID

    This also works and give me the same result as above:
    Select o.OrderID, c.companyName, e.firstname + ' ' + e.lastname EmployeeName, o.orderdate, s.companyName,
    o.Freight, o.shipName, o.ShipAddress, (Select Sum(od.UnitPrice) from [Order Details] od where od.OrderID = o.OrderID)as Amount
    from orders o, customers c, Employees e, Shippers s, [Order Details] od
    where o.CustomerID = c.CustomerID
    and o.EmployeeID = e.EmployeeID
    and o.ShipVia = s.ShipperID
    and o.orderid = od.orderid
    group by o.OrderID, c.companyName, e.FirstName, e.LastName, o.orderdate, s.companyName,
    o.Freight, o.shipName, o.ShipAddress
    order by o.OrderID

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The reason is performance. The select statement in the select clause
    Code:
    (Select Sum(od.UnitPrice) from [Order Details] od where od.OrderID = o.OrderID)as Amount
    will be run for each row of your output. It is not really noticeable when you work with the Northwinds database, but if you get hired by a national retailer with millions of sales, you will really start to notice it.

  6. #6
    Join Date
    Dec 2009
    Posts
    50
    Cool. Thank you

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by jbedson View Post
    Thanks for the reply.

    Question though (for understanding and not for trying to be an ass):
    can you tell me why?
    I redid the query with the GROUP BY set up with all of the non aggregate fields and got the same results. So my guess is that for more complicated cases the DISTINCT wont work and I will need to use the GROUP BY instead. I'm trying to understand why it works in this case or why I should use the GROUP BY. My query below:

    This works and gives me 830 rows:
    Select DISTINCT o.OrderID, c.companyName, e.firstname + ' ' + e.lastname EmployeeName, o.orderdate, s.companyName,
    o.Freight, o.shipName, o.ShipAddress, (Select Sum(od.UnitPrice) from [Order Details] od where od.OrderID = o.OrderID)as Amount
    from orders o, customers c, Employees e, Shippers s, [Order Details] od
    where o.CustomerID = c.CustomerID
    and o.EmployeeID = e.EmployeeID
    and o.ShipVia = s.ShipperID
    and o.orderid = od.orderid
    order by o.OrderID

    This also works and give me the same result as above:
    Select o.OrderID, c.companyName, e.firstname + ' ' + e.lastname EmployeeName, o.orderdate, s.companyName,
    o.Freight, o.shipName, o.ShipAddress, (Select Sum(od.UnitPrice) from [Order Details] od where od.OrderID = o.OrderID)as Amount
    from orders o, customers c, Employees e, Shippers s, [Order Details] od
    where o.CustomerID = c.CustomerID
    and o.EmployeeID = e.EmployeeID
    and o.ShipVia = s.ShipperID
    and o.orderid = od.orderid
    group by o.OrderID, c.companyName, e.FirstName, e.LastName, o.orderdate, s.companyName,
    o.Freight, o.shipName, o.ShipAddress
    order by o.OrderID
    Why did you specified [Order Details] od in FROM clause?


    How about these examples?
    Example 1:
    Code:
    SELECT o.OrderID, c.companyName, e.firstname + ' ' + e.lastname EmployeeName
         , o.orderdate, s.companyName, o.Freight, o.shipName, o.ShipAddress
         , (SELECT Sum(od.UnitPrice) FROM [Order Details] od
             WHERE od.OrderID = o.OrderID) as Amount
     FROM  orders o, customers c, Employees e, Shippers s
     WHERE o.CustomerID = c.CustomerID
       AND o.EmployeeID = e.EmployeeID
       AND o.ShipVia    = s.ShipperID
     ORDER BY
           o.OrderID
    or

    Example 2:
    Code:
    SELECT o.OrderID, c.companyName, e.firstname + ' ' + e.lastname EmployeeName
         , o.orderdate, s.companyName, o.Freight, o.shipName, o.ShipAddress
         , od.Amount
     FROM  orders o, customers c, Employees e, Shippers s
         , (SELECT OrderID , Sum(UnitPrice) as Amount FROM [Order Details]
             GROUP BY OrderID) od
     WHERE o.CustomerID = c.CustomerID
       AND o.EmployeeID = e.EmployeeID
       AND o.ShipVia    = s.ShipperID
       AND od.OrderID   = o.OrderID
     ORDER BY
           o.OrderID

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •