CODEWITHSUNDEEP
sql
user559142
user559142

Reputation: 12517

COUNT(*) in SQL

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?

Upvotes: 8

Views: 22232

Answers (5)

Nicholas Carey
Nicholas Carey

Reputation: 74197

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:

  1. Compute the cartesian product of all tables references in the FROM clause (as if a full join were being performed.
  2. Apply the join criteria.
  3. Filter according to the criteria defined in the where clause.
  4. Partitition into groups, based on the criteria defined in the group by clause.
  5. Reduce each group to a single row, computing the values of each aggregate function on the rows in that group.
  6. Filter according to the criteria defined in the having clause
  7. Sort according to the criteria defined in the order by clause

This 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.

Upvotes: 4

Denis de Bernardy
Denis de Bernardy

Reputation: 78423

It counts the number of rows for each distinct StaffNo in the cartesian product.

Also, you should group by Branch, StaffNo.

Upvotes: 0

BonyT
BonyT

Reputation: 10940

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

Upvotes: 9

JNK
JNK

Reputation: 65147

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.

Upvotes: 3

rajah9
rajah9

Reputation: 12329

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).

Upvotes: 1

Related Questions