List Employees with Higher Than Average Number of Orders
List Employees with Higher Than Average Number of Orders
employees Table:
Orders Table:
SQL Query Explanation:
● SELECT Employees.EmployeeID, CONCAT(Employees.FirstName, ' ', Employees.LastName) AS FullName, COUNT(Orders.OrderID) AS OrderCount
Retrieves the Employee ID, full name (by combining first and last names), and the total number of orders handled by each employee.
● FROM Employees
Starts from the Employees table.
● JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID
Joins the Orders table with Employees using the EmployeeID field to match employees with their orders.
● GROUP BY Employees.EmployeeID, FullName
Groups the result set by each employee to calculate order count individually.
● HAVING COUNT(Orders.OrderID) > ( ... )
Filters the grouped results to include only those employees who processed more than the average number of orders.
● Subquery: (SELECT AVG(OrderCount) FROM (...))
This inner subquery calculates the average number of orders per employee.
- First, it counts orders per employee.
- Then, it calculates the average from those counts.
SQL Query:
SELECT Employees.EmployeeID,
CONCAT(Employees.FirstName, ' ', Employees.LastName) AS FullName,
COUNT(Orders.OrderID) AS OrderCount
FROM Employees
JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID
GROUP BY Employees.EmployeeID, FullName
HAVING COUNT(Orders.OrderID) > (
SELECT AVG(OrderCount) FROM (
SELECT COUNT(*) AS OrderCount
FROM Orders
GROUP BY EmployeeID
) AS AvgSubquery
);
Output:
