skip to Main Content

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.

3

Answers


  1. 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’

    Login or Signup to reply.
  2. 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
    
    Login or Signup to reply.
  3. 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 now 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.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search