Counting rows in joined MySQL tables
Archive - Originally posted on "The Horse's Mouth" - 2010-02-22 08:23:29 - Graham EllisYesterday, I showed you how to join two database tables and get out all matching records, and records which are "orphans" too - records in the left hand table that don't match anything in the right hand table, and vice versa. Yesterday's archive is [here] and our long standing tutorial on joins which explains MySQL joins is [here].
But what if I don't want to get back all the matching records - I just want to know how records in my right hand table match each record in my left hand table?
If - for example I have a table of programming languages and a table of courses that we offer, and I simply want to tell you how many courses I have for each of the languages, as shown here.• An extra clause on my select - GROUP BY - lets me merge all rows after I have done my join which have the same value in a field of my choice:
group by su_id• An extra selected display column counts the number of rows in that grouping with NOT NULL values in a field of your choice:
count(co_id)| Aside - two common errors ... 1. If you group by co_id rather than by su_id - i.e. by the joined column in the table that may be missing matching items - you'll loose all but one of the programming language for which you have no courses, as the join will lump all of these records into one. 2. If you count su_id rather than co_id (the other way around to the first note!), you'll end up claiming one course for each subject on which you don't actually have any courses at all! |
I have chosen to go one step further in my example - I wanted to sort my resulting table to show the subjects on which we offer the most courses first. In order to sort, I need to apply add an alias (a name) to the calculated column so that I can refer to it later in my SQL statement:
count(co_id) as numcoand I can then use an ORDER clause to request sorting:
order by numco desc, su_name(a descending sort, with a sort by the subject name within groups of languages for which we offer the same number of courses)
The complete statement is as follows:
select su_name, su_author, count(co_id) as numco
from dm_courses right join dm_subjects
on co_su = su_id
group by su_id
order by numco desc, su_nameSomewhat unusually, we ran a MySQL training course over the weekend - and this is one of the examples that I was using. We run regular weekday public MySQL courses every few months - see [here] for our current schedule for them. If you've a group of delegate who want the training, we can also arrange private courses too - either at our training centre or on your site.