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
SELECT TOP 1 staff_id, COUNT(*) AS sales FROM orders GROUP BY staff_id ORDER BY sales DESC
it shows that the employee with the ID=6 has the highest number of sales
but when passing this as a subquery:
SELECT * 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
Try the following query instead.
This returns a single expression, which the error is telling you is expected:
SELECT * 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.