Intermediate SQL Tutorial | Having Clause

Key Takeaways

what is going on everybody my name is Alex fabric and today we're gonna be looking at the having clause now they haven't class I feel a little bit unappreciated in the sequel community and I feel like it doesn't get a lot of love and so today I wanted to describe how to use it what it's used for so before we use the having clause I want to set up our query here we want to use an aggregate function in the group by statement and then I will show you how to use this having clause so let's look at the job title and let's look at the count of job titles and then down here we need to do group by job title and let's execute this and here is our job titles and here's the count of how many people have those job titles so now let's say we want to look at all the jobs that have more than one person in that specific job so let's do where the count of job title is greater oops is greater than one and let's run that and as you can see we're gonna get this message right here now let's read it an aggregate may not appear in the where clause unless it is in a sub query contained in a having clause or a select list and the column being aggregated is an outer reference what that is basically saying is is we cannot use this aggregate function in the where statement we need to use a having clause so let's get rid of this and let's say having the count of job title greater than one I did the same thing again and let's execute this and we're still gonna get an error now why are we getting that error the reason is is because this having statement is completely dependent on the group by statement because we're performing this after it has been aggregated so this having statement actually needs to go after the group by statement because we can't look at the aggregated information before it's actually aggregated in that group by statement so now let's run this and it worked perfectly so now we only have the jobs that have more than one employee for job title so now let's look at one more example let's do the average let's say salary and let's get rid of this having Clause real quick and just to look at this information let's do order by and we'll do average salary so let's look at this and we have 36,000 265 thousand so in the middle we got forty four thousand five hundred so let's use this having statement and let's stay the average of salary where it is greater than forty five thousand and we actually need to put this right here right after the group by and before the order by so let's run this and see what we get and it works perfectly so now we're looking at the job titles that have an average salary of over forty five thousand dollars so there you go that is the having clause definitely one that is good to know and is very useful in specific situations thank you guys so much for watching I really appreciate it if you liked this video or learned anything today be sure to subscribe below and I'll see you in the next video