CODEWITHSUNDEEP
sqlcountaverage
Jako
Jako

Reputation: 21

Return the rows that are above average

I'm trying to find the countries from which the number of orders is higher than the average. This is as far as I got. The problem is (see picture) that the count of orders for each country is off, as it should be different for everyone

  SELECT avg(NumberOrders) as avg, 
         Customers.Country, 
         NumberOrders
    FROM Customers,
          (SELECT COUNT(Orders.OrderId) AS NumberOrders 
             FROM Customers JOIN 
                  Orders ON Customers.CustomerID = Orders.CustomerID
         GROUP BY Customers.Country) nested
GROUP BY Customers.Country
  HAVING NumberOrders > avg;

Output

Upvotes: 1

Views: 2979

Answers (1)

dnoeth
dnoeth

Reputation: 60472

If your DBMS supports Windowed Aggregates (almost all besides MySQL & Access):

select *
from
 (
   SELECT Customers.Country, 
      COUNT(Orders.OrderId) AS NumberOrders,  -- count per country
      AVG(COUNT(Orders.OrderId)) OVER () AS avgOrders -- average count
   FROM Customers
   JOIN Orders 
     ON Customers.CustomerID = Orders.CustomerID
   GROUP BY Customers.Country
 ) nested
WHERE NumberOrders > avgOrders

Edit:

For DBMSes not supporting Windowed Aggregates it's way more complicated:

SELECT Customers.Country, 
   COUNT(Orders.OrderId) AS NumberOrders  -- count per country
FROM Customers
JOIN Orders 
  ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.Country
HAVING COUNT(Orders.OrderId) >
 ( select avg(NumberOrders)
   from
    (
      SELECT Customers.Country, 
         COUNT(Orders.OrderId) AS NumberOrders  -- count per country
      FROM Customers
      JOIN Orders 
        ON Customers.CustomerID = Orders.CustomerID
      GROUP BY Customers.Country
    ) AS dt
 )

If the DBMS supports Common Table Expressions this can be simplified: 

with cte as
 (
   SELECT Customers.Country, 
      COUNT(Orders.OrderId) AS NumberOrders  -- count per country
   FROM Customers
   JOIN Orders 
     ON Customers.CustomerID = Orders.CustomerID
   GROUP BY Customers.Country
 ) 
select *
from cte
WHERE NumberOrders >
 (
   select avg(NumberOrders) from cte
 )

Upvotes: 2

Related Questions