SQL query to List the staff name of all male staff who have sold more than 10 vehicles-CodePudding

I have these two tables:

Staff (staffNo(PK), fName, lname, gender, DOB, salary, intTelNo)

CarPurchase (purchaseNo(PK), registrationNo(FK), customerNo(FK), amount, date, staffNo(FK)).

I need an SQL query to list the names of all male staff who have sold more than 10 vehicles.

I tried this:

SELECT S.f.Name, S.l.Name
FROM Staff AS S.InnerJoin Car Purchase AS P ON S.staffNo = P.staffNo
WHERE S.gender= “Male”
HAVING COUNT(DISTINCT P.staffNo) \> 10;

But the query gave me this error:

Oops, the OP forgot to include the error message. Maybe they'll edit the question to fix it.

CodePudding user response：

This should serve the purpose:

SELECT 
  s.staffNo, s.fName 
FROM 
  Staff s 
  JOIN CarPurchase cp ON cp.staffNo = s.staffNo 
WHERE  s.gender = 'Male' 
GROUP BY s.staffNo, s.fName
HAVING count(purchaseNo) > 10

CodePudding user response：

Try this :

SELECT 
  distinct s.fname, 
  count(cp.purchaseNo) 
FROM 
  staff s 
  JOIN carpurchase cp ON cp.staffNo = s.staffno 
WHERE 
  s.gender = 'Male' 
GROUP BY 
  s.fname 
HAVING 
  count(cp.purchaseno) > 10;

I juste assumed that gender does contain 'Male' not 'M' or '1'

CodePudding user response：

I count at least seven obvious typos, shown here with asterisk above each spot:

          *         *
SELECT S.f.Name, S.l.Name
               *    *        * 
FROM Staff AS S.InnerJoin Car Purchase AS P ON S.staffNo = P.staffNo
                *
WHERE S.gender= “Male”
                                 *
HAVING COUNT(DISTINCT P.staffNo) \> 10;

I usually like to be more forgiving about this kind of thing, but seven? The thing is, computers are the ultimate pedantic machines. You will need to learn to be a LOT more precise about these things if you want to find success as a developer.

So here is the original after we clean it up:

SELECT S.fName, S.lName
FROM Staff AS S
Inner Join CarPurchase AS P ON S.staffNo = P.staffNo
WHERE S.gender= 'Male'
HAVING COUNT(DISTINCT P.staffNo) > 10;

This still isn't right, but not at least we're to a place where we can start with meaningful fixes... the first of which is you want to use a GROUP BY to go with the HAVING clause. This GROUP BY should include any fields used in the SELECT clause, and it should be sure to uniquely identify each group (names alone are NOT generally good enough for this). So let's add that:

SELECT S.fName, S.lName
FROM Staff AS S
Inner Join CarPurchase AS P ON S.staffNo = P.staffNo
WHERE S.gender= 'Male'
GROUP BY s.staffNo, s.fName, s.lName
HAVING COUNT(DISTINCT P.staffNo) > 10;

Now we're closer... but the having clause still isn't right. When we group by the staff person, and count the distinct staffNo rows within that group, it's always the same value, and so the HAVING expression reduces to 1 > 10. Instead, we want to count the purchase rows in each group. Again, it's best to base on this on a unique key, like purchaseNo. Do that, and we can also drop the DISTINCT. That brings us to this:

SELECT S.fName, S.lName
FROM Staff AS S
INNER JOIN CarPurchase AS P ON S.staffNo = P.staffNo
WHERE S.gender= 'Male'
GROUP BY s.staffNo, s.fName, s.lName
HAVING COUNT(purchaseNo) > 10;

And now, at least we finally have a working query.