CODEWITHSUNDEEP
sqljoingroup-bysubqueryaverage
Math
Math

Reputation: 429

How can I group by the following sql subquery

How can I group by the following query:

The main query should return three columns: State, City and the average order for each city. To do this, you can group the result set by the State and City columns. Finally, order the result by State and then City in ascending sequence.

   SELECT a.State, a.City, avgOrder = (SELECT AVG(o2.OrderID) 
        FROM Orders AS o2 WHERE o2.CustomerID = a.CustomerID)
   FROM Addresses AS a INNER JOIN Orders AS o ON o.CustomerID = a.CustomerID
   ORDER BY a.State ASC

Table Orders:

   [OrderID],
  ,[CustomerID]
  ,[OrderDate]
  ,[ShipAmount]
  ,[TaxAmount]
  ,[ShipDate]
  ,[ShipAddressID]
  ,[CardType]
  ,[CardNumber]
  ,[CardExpires]
  ,[BillingAddressID]

Table Addresses:

   [AddressID]
  ,[CustomerID]
  ,[Line1]
  ,[Line2]
  ,[City]
  ,[State]
  ,[ZipCode]
  ,[Phone]
  ,[Disabled]

Upvotes: 0

Views: 158

Answers (2)

GMB
GMB

Reputation: 222462

The query you wanted to write probably is:

SELECT 
    a.State, 
    a.City, 
    (
        SELECT AVG(o.??) 
        FROM Orders o WHERE o.CustomerID = a.CustomerID
    ) as avgOrder
FROM Addresses a
ORDER BY a.State, a.City

It is unclear which column you want to average in table orders (but most likely that's not the oderid): I represented it as question marks in the query.

But this does not match the requirement, that describes a join and aggregation. That would be:

SELECT 
    a.State, 
    a.City, 
    AVG(o.??) avgOrder
FROM Addresses a
INNER JOIN Orders o ON o.CustomerID = a.CustomerID
GROUP BY a.State, a.City
ORDER BY a.State, a.City

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Presumably, you want the average oder size per city, but there is no indication of what the size is. Otherwise, the query seems like a JOIN with an aggregation -- and your attempt is more complicated and missing the GROUP BY:

SELECT a.State, a.City, AVG(o.<size column>) as avgOrder
FROM Orders o JOIN 
     Addresses a 
     ON o.CustomerID = a.CustomerID)
GROUP BY a.state, a.city
ORDER BY a.State ASC

Upvotes: 1

Related Questions