0:00
what is going on everybody my name is
0:01
Alex fabric and today we're gonna be
0:03
looking at the having clause now they
0:05
haven't class I feel a little bit
0:07
unappreciated in the sequel community
0:09
and I feel like it doesn't get a lot of
0:10
love and so today I wanted to describe
0:12
how to use it what it's used for
0:14
so before we use the having clause I
0:16
want to set up our query here we want to
0:18
use an aggregate function in the group
0:20
by statement and then I will show you
0:21
how to use this having clause so let's
0:25
look at the job title and let's look at
0:28
the count of job titles and then down
0:33
here we need to do group by job title
0:37
and let's execute this and here is our
0:42
job titles and here's the count of how
0:44
many people have those job titles so now
0:47
let's say we want to look at all the
0:48
jobs that have more than one person in
0:50
that specific job so let's do where the
0:56
count of job title is greater
1:01
oops is greater than one and let's run
1:05
that and as you can see we're gonna get
1:08
this message right here now let's read
1:09
it an aggregate may not appear in the
1:12
where clause unless it is in a sub query
1:14
contained in a having clause or a select
1:17
list and the column being aggregated is
1:20
an outer reference what that is
1:23
basically saying is is we cannot use
1:25
this aggregate function in the where
1:27
statement we need to use a having clause
1:29
so let's get rid of this and let's say
1:32
having the count of job title greater
1:38
than one I did the same thing again and
1:40
let's execute this and we're still gonna
1:43
get an error now why are we getting that
1:45
error the reason is is because this
1:47
having statement is completely dependent
1:50
on the group by statement because we're
1:52
performing this after it has been
1:53
aggregated so this having statement
1:55
actually needs to go after the group by
1:58
statement because we can't look at the
2:01
aggregated information before it's
2:03
actually aggregated in that group by
2:05
statement so now let's run this and it
2:08
worked perfectly so now we only have the
2:11
jobs that have more than one employee
2:13
for
2:13
job title so now let's look at one more
2:17
example let's do the average let's say
2:20
salary and let's get rid of this having
2:23
Clause real quick and just to look at
2:27
this information
2:28
let's do order by and we'll do average
2:34
salary so let's look at this and we have
2:40
36,000 265 thousand so in the middle we
2:43
got forty four thousand five hundred so
2:45
let's use this having statement and
2:46
let's stay the average of salary where
2:55
it is greater than forty five thousand
2:59
and we actually need to put this right
3:02
here right after the group by and before
3:05
the order by so let's run this and see
3:08
what we get and it works perfectly so
3:11
now we're looking at the job titles that
3:12
have an average salary of over forty
3:14
five thousand dollars so there you go
3:16
that is the having clause definitely one
3:18
that is good to know and is very useful
3:20
in specific situations thank you guys so
3:23
much for watching I really appreciate it
3:25
if you liked this video or learned
3:26
anything today be sure to subscribe
3:28
below and I'll see you in the next video