CODEWITHSUNDEEP
sqlsql-server
DoktorAgon
DoktorAgon

Reputation: 69

Northwind - List of top ten employees

I have another trouble with my SQL queries.

My task is to create a list of top 10 employees, with the most sales in 1997. So far I have this, simple query that shows me list of employees and which order they've sold.

SELECT  
    Orders.EmployeeID,
    Orders.OrderID
FROM
    Employees
JOIN 
    Orders ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY 
    Orders.EmployeeID;

Now I want to group up those numbers, I need to know how many sales each employee did in 1997. How to do that?

Upvotes: 1

Views: 1173

Answers (3)

paparazzo
paparazzo

Reputation: 45096

It can reduce to this

SELECT  top(10) EmployeeID, count(*) as cnt
FROM  Orders 
group by EmployeeID 
ORDER BY count(*) desc;

Upvotes: 0

Jay Shankar Gupta
Jay Shankar Gupta

Reputation: 6088

WITH CTE AS 
(
SELECT EmployeeId,COUNT(*) as cn,
       DENSE_RANK(ORDER BY COUNT(*) DESC) AS rn
FROM orders
WHERE DATEPART(year,OrderDate)=1997
GROUP BY EmployeeId
) 
SELECT e.*,COALESCE(o.cn,0) AS CountOrders
FROM Employees e
LEFT JOIN CTE o
ON e.EmployeeId=o.EmployeeId
WHERE o.rn<=10

Upvotes: 0

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726939

You can get the results that you need without JOIN and GROUP BY if you order by results of a subquery:

SELECT TOP 10 *
FROM Employees e
ORDER BY (
    SELECT COUNT(*)
    FROM Sales s
    WHERE s.EmployeeId=e.EmployeeId
      AND DATEPART(year, o.OrderDate)=1997
) DESC

This yields top ten employees by the number of sales transactions.

If you need anything from Sales, say, the count, you would need to go the GROUP BY route:

SELECT TOP 10 * FROM (
    SELECT e.EmployeeId, COUNT(*) AS SalesCount
    FROM Employees e
    LEFT OUTER JOIN Orders o ON o.EmployeeId=e.EmployeeId
                            AND DATEPART(year, o.OrderDate)=1997
    GROUP BY e.EmployeeId
) groups
ORDER BY SalesCount DESC

Upvotes: 2

Related Questions