By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.26 |
create table Employees (EmployeeID int primary key, FirstName varchar(100), LastName varchar(100));
insert into Employees values (1, 'John', 'Smith'), (2, 'Mary', 'Miller');
create table Products (ProductID int primary key, ProductName varchar(100));
insert into Products values (1, 'Product 1'), (2, 'Product 2');
create table Orders (OrderID int primary key, EmployeeID int);
insert into Orders values (1, 1), (2, 1), (3, 2), (4, 2);
create table OrderDetails (OrderID int, ProductID int);
insert into OrderDetails values (1, 1), (1, 2), (2, 2), (3, 1), (4, 1), (4, 2);
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;
| Employee name | Num of sales | Product Name |
|---|---|---|
| John Smith | 3 | Product 2 |
| Mary Miller | 3 | Product 1 |
SELECT
concat_ws(' ',
e.FirstName,
e.LastName) as "Employee name",
count(*) as "Num of sales"
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;
| Employee name | Num of sales |
|---|---|
| John Smith | 3 |
| Mary Miller | 3 |
SET sql_mode = '';
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;
| Employee name | Num of sales | Product Name |
|---|---|---|
| John Smith | 3 | Product 2 |
| Mary Miller | 3 | Product 1 |