CODEWITHSUNDEEP
mysqlsql
Leem.fin
Leem.fin

Reputation: 42692

get average number of count

I have a Shippers table and a Orders table. To get the number of orders sent by each shipper, I use the following statement:

SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers
ON Orders.ShipperID=Shippers.ShipperID
GROUP BY ShipperName;

It works fine. Based on the result of the above statement, how can I get the average number of orders sent by each shipper?

Upvotes: 2

Views: 159

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

One method is a subquery:

SELECT AVG(NumberOfOrders)
FROM (SELECT s.ShipperName, COUNT(o.OrderID) AS NumberOfOrders
      FROM Orders o LEFT JOIN
           Shippers s
           ON o.ShipperID = s.ShipperID
      GROUP BY s.ShipperName
     ) t;

However, a simpler way is to just do the division yourself:

SELECT COUNT(o.OrderID) / COUNT(DISTINCT s.ShipperName)
FROM Orders o LEFT JOIN
     Shippers s
     ON o.ShipperID = s.ShipperID;

Note: Two reasonable assumptions are that each shipper has its own id and that all shippers have at least one order. If so, you can simplify this to:

SELECT COUNT(o.OrderID) / COUNT(DISTINCT l.ShipperId)
FROM Orders o;

Upvotes: 0

Luca Rainone
Luca Rainone

Reputation: 16468

Try with a subquery

SELECT myT.*, AVG(NumberOfOrders) FROM(

    SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
    LEFT JOIN Shippers
    ON Orders.ShipperID=Shippers.ShipperID
    GROUP BY ShipperName;

) myT

Upvotes: 0

Christiaan Janssen
Christiaan Janssen

Reputation: 1053

You achieve this by using the AVG() function:

SELECT Shippers.ShipperName, AVG(Orders.OrderID) AS AvgNumberOfOrders 
FROM Orders
LEFT JOIN Shippers
ON Orders.ShipperID=Shippers.ShipperID
GROUP BY ShipperName;

More information: http://www.w3schools.com/sql/sql_func_avg.asp

Upvotes: 1

Related Questions