using MYSQL Group By to get the most popular value

user_16679301 0
Viewed 103

I am practicing MYSQL using https://www.w3schools.com/mysql/trymysql.asp?filename=trysql_func_mysql_concat which has a mock database for me to practice with an I am experimenting using the GROUP BY command I am attempting to group all employees up with all of their sales and determine, their name, their amount of sales and the product that they sold the most. I have managed to get their name and sales but not the product name. I know that extracting information with a group by is difficult and I have tried using a sub query. Is there a way to get the information. My query is below.

SELECT 
    CONCAT_WS(' ',
            Employees.FirstName,
            Employees.LastName) AS 'Employee name',
    COUNT(*) AS 'Num of sales'
FROM
    Orders
        INNER JOIN
    Employees ON Orders.EmployeeID = Employees.EmployeeID
        INNER JOIN
    OrderDetails ON OrderDetails.OrderID = Orders.OrderID
        INNER JOIN
    Products ON Products.ProductID = OrderDetails.ProductID
GROUP BY Orders.EmployeeID
ORDER BY COUNT(*) DESC;

What this says is get orders, join employees based on orders employeeid, join the order details based on order id and join products information based on product id in the order details, then it groups them by the employee id and orders them by the number of sales an employee has made.

SELECT 
  concat_ws(' ',
           Employees.FirstName,
           Employees.LastName) as 'Employee name',
  count(*) as 'Num of sales',
  (
    SELECT Products.ProductName 
    FROM Orders 
    INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID 
    INNER JOIN OrderDetails ON OrderDetails.OrderID = Orders.OrderID 
    INNER JOIN Products ON Products.ProductID = OrderDetails.ProductID 
    GROUP BY Orders.EmployeeID 
    ORDER BY count(Products.ProductName) desc
    LIMIT 1
  ) as 'Product Name'
FROM Orders 
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID 
INNER JOIN OrderDetails ON OrderDetails.OrderID = Orders.OrderID 
INNER JOIN Products ON Products.ProductID = OrderDetails.ProductID 
GROUP BY Orders.EmployeeID 
ORDER BY count(*) desc;

Above is my attempt at using a sub query for the solution.

aggregate select group-by mysql sql
2 Answers

It is quite ugly, as the w3school uses still mysql 5.7

On a personal note, you should install your own server grab somewhere a database and test it there, in mysql workbench you can have many query tabs in which you can test queries , till you het the "right" result.

SELECT 
    CONCAT_WS(' ',
            Employees.FirstName,
            Employees.LastName) AS 'Employee name',
    COUNT(*) AS 'Num of sales',
    tn.ProductName
FROM
    Orders
        INNER JOIN
    Employees ON Orders.EmployeeID = Employees.EmployeeID
        INNER JOIN
    OrderDetails ON OrderDetails.OrderID = Orders.OrderID
        INNER JOIN
    Products ON Products.ProductID = OrderDetails.ProductID
 INNEr JOIN 
    (SELECT EmployeeID, p.ProductName
    FROM (SELECT IF (@Eid = EmployeeID ,@rn := @rn +1, @rn := 1) rn,ProductID,  sumamount
    , @Eid := EmployeeID  as EmployeeID
    FROM
    (
SELECT
    EmployeeID,ProductID, SUM(Quantity) sumamount
    FROM Orders o INNER JOIN OrderDetails od ON od.OrderID = o.OrderID,(SELECT @Eid := 0, @rn := 0) t1
    GROUP BY EmployeeID,ProductID
    ORDER BY EmployeeID,sumamount DESC ) t2 ) t3
    INNER JOIN Products p ON t3.ProductID = p.ProductID
    WHERE rn= 1) tn 
    ON Orders.EmployeeID = tn.EmployeeID
GROUP BY Orders.EmployeeID
ORDER BY COUNT(*) DESC;
user_51935360

In your second query you are trying to get an employee's most often sold product. But there are two mistakes in that subquery:

  1. The subquery is invalid. You group by employee, but select a product. Which product? An employee can sell many different products. MySQL should raise a syntax error here, as all other DBMS I know of do. But you are in cheat mode. MySQL allows incorrect aggregation queries and silently applies ANY_VALUE on all columns that cannot be selected otherwise. Thus you are selecting ANY_VALUE(Products.ProductName), i.e. a product arbitrarily chosen by the DBMS. To get out of cheat mode SET sql_mode = 'ONLY_FULL_GROUP_BY';.
  2. Then, you don't relate the subquery to your main query. So when selecting the row for, say, employee #123, your subquery still selects data for all employees in order to pick one of their products. And as this is independent from the employee in the main query, it will probably pick the same product for every other employee you are selecting, too.

Here is what the query should look like instead:

SELECT 
  concat_ws(' ', e.FirstName, e.LastName) as "Employee name",
  count(*) as "Num of sales",
  (
    SELECT p2.ProductName 
    FROM Orders o2
    INNER JOIN OrderDetails od2 ON od2.OrderID = o2.OrderID 
    INNER JOIN Products p2 ON p2.ProductID = od2.ProductID 
    WHERE o2.EmployeeID = o.EmployeeID
    GROUP BY p2.ProductID
    ORDER BY count(*) DESC
    LIMIT 1
  ) as "Product Name"
FROM Orders o 
INNER JOIN Employees e ON o.EmployeeID = e.EmployeeID 
INNER JOIN OrderDetails od ON od.OrderID = o.OrderID 
GROUP BY o.EmployeeID 
ORDER BY count(*) desc;

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f35e96764d454a4032d7778b550fc6b4

Disclaimer: When an employee sold more than one product most often (e.g. 500 x product A, 500 x product B, 200 x product C), then one of them (A or B in the example) gets picked arbitrarily for the employee.

user_22707620
Related