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
Try this :
I juste assumed that gender does contain ‘Male’ not ‘M’ or ‘1’
This should serve the purpose:
I count at least seven obvious typos, shown here with asterisk above each spot:
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:
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 theHAVING
clause. ThisGROUP BY
should include any fields used in theSELECT
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: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, likepurchaseNo
. Do that, and we can also drop theDISTINCT
. That brings us to this:And now, at least we finally have a working query.