Reputation: 42692
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
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
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
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