Show employee with highest number of sales

I would like to show the employee with the highest number of sales

i used a subquery to count every employee’s sales, and showed the top one, then used that to show that employee’s information

TOP 1 staff_id, 
AS sales
FROM orders
GROUP BY staff_id 

it shows that the employee with the ID=6 has the highest number of sales
but when passing this as a subquery:

FROM staffs 
WHERE (staff_id =
((select top 1 staff_id, count(*) as sales from orders group by staff_id order by sales desc )))

i get the follwing error:
**only one expression can be specified in the select when the subquery is not introduced with exists

>Solution :

Try the following query instead.

This returns a single expression, which the error is telling you is expected:

FROM staffs 
WHERE staff_id in (
  SELECT TOP (1) staff_id
  FROM orders 
  GROUP BY staff_id
  ORDER BY count(*) DESC

You might also want to look at the with_ties clause to handle where there are duplicate counts.

Leave a Reply