I understand how count(*) in SQL when addressing one table but how does it work on inner joins?
e.g.
SELECT branch, staffNo, Count(*)
FROM Staff s, Properties p
WHERE s.staffNo = p.staffNo
GROUP BY s.staffNo, p.staffNo
staff contains staffNo staffName
properties contains property management details (i.e. which staff manages which property)
This returns the number of properties managed by staff, but how does the count work? As in how does it know what to count?
It's an aggregate function - as such it's managed by your group by clause - each row will correspond to a unique grouping (i.e. staffNo) and Count(*) will return the number of records in the join that match that grouping.
So for example:
SELECT branch, grade, Count(*)
FROM Staff s, Properties p
WHERE s.staffNo = p.staffNo
GROUP BY branch, grade
would return the number of staff members of a given grade at each branch.
SELECT branch, Count(*)
FROM Staff s, Properties p
WHERE s.staffNo = p.staffNo
GROUP BY branch
would return the total number of staff members at each branch
SELECT grade, Count(*)
FROM Staff s, Properties p
WHERE s.staffNo = p.staffNo
GROUP BY grade
would return the total number of staff at each grade
The aggregate function (whether it's count(), sum(), avg(), etc.) is computed on the rows in each group: that group is then collapsed/summarized/aggregated to a single row according to the select-list defined in the query.
The conceptual model for the execution of a select query is this:
full join were being performed.where clause.group by clause.having clauseorder by clauseThis conceptual model omits dealing with any compute or compute...by clauses.
Not this this is not actually how anything but a very naive SQL engine would actually execute a query, but the results should be identical to what you'd [eventually] get if you did it this way.
Your query is invalid.
You have an ambiguous column name staffno.
You are selecting branch but not grouping by it - prepare for a Syntax error (everything but MySQL) or random branches to be selected for you (MySQL).
I think what you want to know, though, is that it will return a count for each "set" of your grouped-by fields, so for each combination of s.staffno, p.staffno how many rows belong in that set.
count (*) simply counts the number of rows in the query or the group by.
In your query, it will print the number of rows by staffNo. (It is redundant to have s.staffNo, p.staffNo; either will suffice).
It counts the number of rows for each distinct StaffNo in the cartesian product.
Also, you should group by Branch, StaffNo.
