Mysql Question posted in
The official MySQL documentation can be found here.

MySQL queries to find the person with most sales isn't working

I have been trying to get the name of employees first name for all sales of the sales person with the most sales.

I have tried these 2 solutions but none of them seems to work.

(P.S. I will provide more information if this is not enough)

SELECT s.date, st.item, st.price, st.category, e.first_name as firstname, COUNT(*) AS allsales
FROM sales s INNER JOIN employees e ON s.employee = e.id
INNER JOIN stock_items st ON s.item = st.id
GROUP BY firstname ORDER BY s.date DESC LIMIT 1;

SELECT s.date, st.item, st.price, st.category, e.first_name as firstname, COUNT(*) AS allsales
FROM sales s INNER JOIN employees e ON s.employee = e.id
INNER JOIN (SELECT COUNT(*) AS allsales, e.first_name AS firstname, 
FROM sales s INNER JOIN employees e ON s.employee = e.id 
GROUP BY firstname ORDER BY allsales DESC LIMIT 1) as t ON e.first_name = t.firstname;
Tags:

3

Answers


  1. 0

    This query first finds the employee with the most sales, and then selects all the sales of that employee

    SELECT s.date, st.item, st.price, st.category, e.first_name as firstname
FROM sales s 
INNER JOIN employees e ON s.employee = e.id
INNER JOIN stock_items st ON s.item = st.id
WHERE e.first_name = (
  SELECT e.first_name
  FROM sales s 
  INNER JOIN employees e ON s.employee = e.id
  GROUP BY e.first_name
  ORDER BY COUNT(*) DESC 
  LIMIT 1
);
    Login or Signup to reply.
  2. 0

    Assuming each sale results in exactly one record in sales:

    SELECT e.id, e.first_name as firstname, COUNT(*) AS allsales FROM sales s  
JOIN employees e ON s.employee = e.id
GROUP BY e.id, e.first_name
ORDER BY 3 DESC

    This returns the employee id, firstname and the amount of sales.

    Login or Signup to reply.
  3. 0

    when using aggregation functions like count(*) you must include all the non aggregated selected columns in the group by

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search