CB Moate CB Moate - 6 months ago 28
MySQL Question

SQL COUNT() query placement

I'm trying to display all the names of all salespeople that have sold 10 or more orders for the sake of giving them bonuses at the end of the month.

These are my table schema

CREATE TABLE Salesperson
(
SalespersonId BIGINT,
Name VARCHAR(50),
BirthDate DATE,
Salary INT
);

CREATE TABLE Orders
(
OrderId BIGINT,
OrderDate DATETIME,
SalespersonId BIGINT,
ProductId BIGINT,
SalesPrice DECIMAL(9,2)
);


And then this is my query

SELECT Salesperson.Name
FROM Salesperson
JOIN Orders
ON Salesperson.SalespersonId=Orders.SalespersonId
WHERE COUNT(Order.SalespersonId)> 10


I'm not sure where I should be placing the
COUNT()
function in this to make it return the list of Salesperson names.

Answer

You need to GROUP BY, then use the HAVING clause to filter:

SELECT Salesperson.Name 
FROM Salesperson
JOIN Orders
ON Salesperson.SalespersonId=Orders.SalespersonId 
GROUP BY Salesperson.Name 
HAVING COUNT(Order.SalespersonId)> 10

The predicate used in the HAVING clause:

COUNT(Order.SalespersonId)> 10

is applied to each Salesperson.Name group. So it will filter out any groups having a population that is equal to 10 or less.

Comments