Back in August of 2020, The Many Flavors of the SQL Count() Function provided an overview of COUNT's many input parameter variations. Another way to use the COUNT() function is to combine it with the GROUP BY clause. Using the COUNT() function in conjunction with GROUP BY is useful for breaking down counts according to various groupings. In today's blog, we'll learn how to group counts by different criteria by querying the Sakila Sample Database using Navicat Premium as our database client.
Case 1: Actors Who Have Appeared In Most PG Movies
By itself, the COUNT() function could tell us how many actors have appeared in PG Movies. However, if we wanted to know how many PG movies each actor has appeared in, we would need to add the actor_id to the GROUP BY clause. Recall that the GROUP BY clause groups records into summary rows and returns one record for each group. GROUP BY queries often include aggregate functions such as COUNT, MAX, SUM, AVG, etc.
Here is the SELECT statement, along with the query results, as shown in Navicat Premium:
Notice that, when using GROUP BY, we can also order records by counts in descending order so that actors with the highest number of PG films in their filmography appear at the top of the results.
Case 2: Number of Films Rented Per Day
Applying the COUNT() function to the rental table can tell us how many movies have been rented in total. For more detailed counts, we need to turn to the GROUP BY clause. For example, we can break down counts by individual days by grouping by the rental_date. We also have to specify that the return_date must not be NULL, so that we don't count movies that have been rented but not yet returned.
In this case, ORDER BY is not required because results are automatically ordered by the grouped column, i.e., the rental date.
Case 3: Number of Films Rented by Customer Per Month
After a couple of relatively simple examples, it's time to ratchet up the level of difficulty a bit. A GROUP BY clause can group on multiple fields to obtain even more fine-grained tabulations. Case in point, this query counts movie rentals for each customer, month, and year:
In this case, we included the ORDER BY to sort results by customers' last names (last_name) rather than by customer_id. A similar thing is being done with the months in that month names are displayed, but grouped and ordered according to the month number.
Conclusion
In today's blog, we learned how to group counts by different criteria by querying the Sakila Sample Database using Navicat Premium. As we saw here today, using the COUNT() function in conjunction with GROUP BY is useful for breaking down counts according to various groupings. In fact, you'd be hard-pressed to obtain the same data without combining COUNT() with GROUP BY!
Interested in Navicat Premium? You can try it for 14 days completely free of charge for evaluation purposes!
Rob Gravelle resides in Ottawa, Canada, and has been an IT Guru for over 20 years. In that time, Rob has built systems for intelligence-related organizations such as Canada Border Services and various commercial businesses. In his spare time, Rob has become an accomplished music artist with several CDs and digital releases to his credit.
