HAVING Examples

Added: 2026-05-13

[00:00:05]welcome back in this video we'll work on a few example queries that use the having clause to understand how it works in our first example query we want to find the ideas of the movies with more than a hundred actors how can we write the spirit first in first we are going to use the roles table as this is the table that contains the information about movies and actors let's write first the Kure that returns the number of actors per movie maybe we write select movie ID comma count act ready as number factors from roles group by movie title now this query counts the number of actor IDs per movie le we could have easily used count star as well but I put down count actor ID to make it a little bit more explicit that we're counting countries top in the results for each movie ID we get back the number of factors so we get movie ID so we get that movie zero has two actors movie two has 20 actors movie three has four actors and so on so now let's say that we don't want to see movies that have less than a hundred actors since the number of factors is the result of an aggregate functions we need to use the having to specify this condition so we add having count actor ID greater than a hundred this query will take a little bit of time around 30 seconds and after the time you will see that we get the movies and the number of actors if we want we can even order by the number of factors by typing quarter by count actor IV descending and alternatively we can even write order by factors descending which uses the Elias that we used in the Select Clause if you want to avoid repeating the ugly it so you can see that we have movie ID 2-0 625 with 1274 actors on top and then the rest of the movies and if you see at the metadata you will see that we have 549 movies that have more than 100 actors let's do one more Curie we want to find the first name of factors that appear more than thousand times this information is in the actors table so we write select from actors and what we want to do want to count how many times a first name appear so the grouping by attribute is going to be first name and the aggregate function counts so select first name comma count star as seen t you execute the query and you'll see that we get now the frequency of its first name and you'll see that we have quite a few names that are rather infrequent and appear just once now what we want to do is want to limit the results to only so as frequent first name where frequent means that they have to appear more than a thousand times in the database there are more than a thousand actors that have this first name so we go now dissin aggregate we use the having clause so we type having count star greater than a thousand now you will see the results contain relatively more common names okay dot Alex and Rihanna and Anthony Antonio Barbara bill Bob catalyst and so on you will see that all of them appear more than a thousand times finally let's expand this query and specify that we want to count its name by gender as well so we go to this curie and we type select first name coma gender comma count star as seen t from actors and I'm going to expand now the group by as well so group by first name comma gender and we keep the condition having count star greater than a thousand now the count stir aggregate counts the frequency broken down by first name in gender so notice also that the count star that we have been having counts the frequency of the more specific gender name group which is typically less than the count that we had when we only use the first name as a grouping attribute now this concludes our discussion on aggregates we have seen how we can use aggregate functions group by and carrick in the next and final part of this course we are going to work on putting everything together select where joins and aggregates and we are going to examine a few more advanced concepts so until then play around and practice see you next time