Yesterday, 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 numco
and 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_name

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


(written 2010-02-22, updated 2010-03-03)

 S157 - More MySQL commands
  [158] MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN - (2004-12-20)
  [159] MySQL - Optimising Selects - (2004-12-21)
  [279] Getting a list of unique values from a MySQL column - (2005-04-14)
  [449] Matching in MySQL - (2005-09-24)
  [494] MySQL - a score of things to remember - (2005-11-12)
  [502] SELECT in MySQL - choosing the rows you want - (2005-11-22)
  [513] MySQL - JOIN or WHERE to link tables correctly? - (2005-12-01)
  [515] MySQL - an FAQ - (2005-12-03)
  [517] An occasional chance, and reducing data to manageable levels - (2005-12-04)
  [567] Combining similar rows from a MySQL database select - (2006-01-17)
  [572] Giving the researcher power over database analysis - (2006-01-22)
  [581] Saving a MySQL query results to your local disc for Excel - (2006-01-29)
  [591] Key facts - SQL and MySQL - (2006-02-04)
  [673] Helicopter views and tartans - (2006-04-06)
  [1213] MySQL - the order of clauses and the order of actions - (2007-06-01)
  [1235] Outputting numbers as words - MySQL with Perl or PHP - (2007-06-17)
  [1331] MySQL joins revisited - (2007-09-03)
  [1574] Joining MySQL tables revisited - finding nonmatching records, etc - (2008-03-15)
  [1735] Finding words and work boundaries (MySQL, Perl, PHP) - (2008-08-03)
  [1904] Ruby, Perl, Linux, MySQL - some training notes - (2008-11-23)
  [2110] MySQL - looking for records in one table that do NOT correspond to records in another table - (2009-03-31)
  [2259] Grouping rows for a summary report - MySQL and PHP - (2009-06-27)
  [2448] MySQL - efficiency and other topics - (2009-10-10)
  [2643] Relating tables with joins in MySQL - (2010-02-21)
  [2645] Optimising and caching your MySQL enquiries - (2010-02-22)
  [2647] Removing duplicates from a MySQL table - (2010-02-22)
  [3061] Databases - why data is split into separate tables, and how to join them - (2010-11-20)
  [3270] SQL - Data v Metadata, and the various stages of data selection - (2011-04-29)
  [4481] Extracting data from backups to restore selected rows from MySQL tables - (2015-05-01)

Back to Relating tables with joins in MySQL

Previous and next or Horse's mouth home

Forward to Optimising and caching your MySQL enquiries

Java arrays - are they true arrays or not?
Compile but do not run PHP - syntax check only
Counting rows in joined MySQL tables
What does a web application look like under Tomcat?
Object Oriented Programming in PHP
How is your tax pound spent?
su or su - ... what is the difference?