JOINS - Complex grouping

Grouping across more than 2 tables

Let’s say we want to display the names of all unique courses and the total number of unique authors who have written books for each course.

In this case, we can use COUNT over a GROUP BY clause on results of a LEFT JOIN across three tables: courses, books and authors. Run the query below.

The results look as intended, but if you notice closely, are wrong. Let’s look at the books table. All books belonging to the Java course are written by one and the same author. But our results for the above query for Java, show 7. The same is true for a few other courses. Lets understand why is that.

In the above query, we have simply COUNTed the id of authors who are attached to books belonging to a course, say Java. There are 7 books that belong to Java, and hence the results are also 7, because we don’t take into account that all books belonging to Java may not be written by distinct authors, which is the case.

The solution is taking uniqueness into account while counting the authors, i.e. adding a DISTINCT clause to the COUNT of author’s ids. Run the query below to get the right results.

Notice that we are grouping the results from authors as a COUNT, on the basis of a grouping parameter from courses (name), two tables which are not directly related. But we are achieving that, by combining them with the help of table that relates to both, books.