Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
0
1
00:00:02,816 --> 00:00:08,448
Now we know, how to use group by and how to use aggregate functions within group by
1
2
00:00:09,472 --> 00:00:13,568
Now we want to put a filter or specify a condition
2
3
00:00:13,824 --> 00:00:15,616
On this aggregate functions
3
4
00:00:16,384 --> 00:00:17,408
How can we do that
4
5
00:00:19,200 --> 00:00:21,248
This is done using the having clause
5
6
00:00:22,272 --> 00:00:28,416
Having clause will restrict the groups of returned rows to only those whose condition is true
6
7
00:00:29,440 --> 00:00:31,232
The syntax for having clause is
7
8
00:00:31,488 --> 00:00:34,560
Select the column names or the aggregate function
8
9
00:00:35,840 --> 00:00:36,608
From table
9
10
00:00:37,120 --> 00:00:39,680
You can specify where conditions if you want to
10
11
00:00:40,448 --> 00:00:41,984
Then you specify the group by
11
12
00:00:42,240 --> 00:00:44,544
And Name the column with which you want to group
12
13
00:00:45,056 --> 00:00:47,616
Then we specify the having condition
13
14
00:00:50,944 --> 00:00:52,224
Let us see this example
14
15
00:00:54,016 --> 00:00:55,040
IN this example
15
16
00:00:55,552 --> 00:00:58,368
I want to find the number of customers
16
17
00:00:58,880 --> 00:01:00,160
In each region
17
18
00:01:00,928 --> 00:01:05,280
But I want to also put condition that I want only those regions
18
19
00:01:05,536 --> 00:01:07,328
Which have more than 200 customers
19
20
00:01:09,120 --> 00:01:09,888
To do this
20
21
00:01:10,400 --> 00:01:12,448
I'll write select region
21
22
00:01:12,704 --> 00:01:13,984
And count of customers
22
23
00:01:14,496 --> 00:01:16,032
From the customer table
23
24
00:01:16,288 --> 00:01:17,568
Group by region
24
25
00:01:18,336 --> 00:01:22,176
And having count of customer ID greater than 200
25
26
00:01:22,432 --> 00:01:24,224
This is the aggregate function
26
27
00:01:25,248 --> 00:01:27,808
So let us go and write this in the PG admin
27
28
00:01:30,112 --> 00:01:32,416
So, I'll select
28
29
00:01:34,464 --> 00:01:40,608
The region and the count of customer ID
29
30
00:01:41,888 --> 00:01:44,960
From
30
31
00:01:45,216 --> 00:01:51,104
As customer count from
31
32
00:01:52,384 --> 00:01:58,528
The table customer group by
32
33
00:01:59,296 --> 00:02:02,112
Region
33
34
00:02:03,136 --> 00:02:05,952
Having
34
35
00:02:06,208 --> 00:02:08,768
Count of customer ID
35
36
00:02:09,024 --> 00:02:11,584
Greater than
36
37
00:02:13,376 --> 00:02:16,448
Greater than 200
37
38
00:02:19,264 --> 00:02:25,408
This query will return me these two regions which have customers
38
39
00:02:25,664 --> 00:02:27,712
more than 200
39
40
00:02:30,016 --> 00:02:31,296
Now if you remember
40
41
00:02:31,552 --> 00:02:35,136
We used to specify conditions in the where clause
41
42
00:02:36,160 --> 00:02:38,976
Now we are specifying this condition in the having clause
42
43
00:02:39,232 --> 00:02:40,256
What is the difference
43
44
00:02:41,280 --> 00:02:45,632
The having Clause is used to specify conditions for the aggregate function
44
45
00:02:46,400 --> 00:02:47,168
Whereas
45
46
00:02:47,424 --> 00:02:49,984
Where was used to specific conditions on the
46
47
00:02:50,496 --> 00:02:51,776
Non-aggregate columns
47
48
00:02:52,800 --> 00:02:55,104
So for example in this query only
48
49
00:02:55,616 --> 00:03:01,760
If, I want to put a condition that I want only count of customers whose name starts with A
49
50
00:03:03,552 --> 00:03:05,088
Then I will write where
50
51
00:03:06,112 --> 00:03:07,392
Customer name
51
52
00:03:07,904 --> 00:03:09,952
Like A percent
52
53
00:03:12,512 --> 00:03:17,120
So let us find out
53
54
00:03:17,632 --> 00:03:23,520
Such customers, count of customer ID
54
55
00:03:28,896 --> 00:03:30,944
As customer count
55
56
00:03:37,344 --> 00:03:43,232
From customer
56
57
00:03:43,488 --> 00:03:45,536
Where
57
58
00:03:45,792 --> 00:03:47,840
Customer name
58
59
00:03:49,632 --> 00:03:53,216
Like
59
60
00:03:54,496 --> 00:03:58,080
A percent
60
61
00:04:03,200 --> 00:04:04,480
Group by region
61
62
00:04:07,808 --> 00:04:10,112
by region
62
63
00:04:10,624 --> 00:04:12,416
Lets run this
63
64
00:04:15,232 --> 00:04:21,375
this is the count of customers whose name
64
65
00:04:21,631 --> 00:04:23,423
Starts with A in
65
66
00:04:23,679 --> 00:04:26,239
These four different regions
66
67
00:04:28,031 --> 00:04:33,151
I can put the having condition also
67
68
00:04:33,407 --> 00:04:37,247
Having count of customer ID
68
69
00:04:37,503 --> 00:04:43,647
Greater than 15, so if you look at this table below 2 of the field
69
70
00:04:43,903 --> 00:04:44,671
Will go
70
71
00:04:44,927 --> 00:04:47,231
And two will remain which are greater than 15
71
72
00:04:47,743 --> 00:04:53,631
So, Two major differences, one where is written before group by and having his return after group by
72
73
00:04:54,143 --> 00:04:54,911
Second
73
74
00:04:55,167 --> 00:05:01,311
Having is used to put condition on aggregate functions whereas condition on non aggregate columns are put
74
75
00:05:01,567 --> 00:05:02,847
Using the where clause
5524
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.
Afrikaans
Albanian
Amharic
Arabic
Armenian
Azerbaijani
Basque
Belarusian
Bengali
Bosnian
Bulgarian
Catalan
Cebuano
Chichewa
Chinese (Simplified)
Chinese (Traditional)
Corsican
Croatian
Czech
Danish
Dutch
English
Esperanto
Estonian
Finnish
Frisian
Galician
Georgian
German
Greek
Gujarati
Haitian Creole
Hawaiian
Hungarian
Icelandic
Indonesian
Irish
Italian
Japanese
Kazakh
Khmer
Korean
Kurdish (Kurmanji)
Kyrgyz
Lao
Latvian
Lithuanian
Luxembourgish
Macedonian
Malagasy
Malay
Maltese
Maori
Mongolian
Myanmar (Burmese)
Nepali
Norwegian
Pashto
Polish
Portuguese
Romanian
Russian
Samoan
Serbian
Sesotho
Shona
Sinhala
Slovak
Slovenian
Somali
Spanish
Swahili
Swedish
Tajik
Thai
Turkish
Ukrainian
Urdu
Uzbek
Vietnamese
