All language subtitles for 1. GROUP BY

Till now we have discussed the aggregate functions
1

Which includes sum, count, average, Min Max functions
2

These functions are mostly used along with the group by clause
3

Group by clause is used to group the results by one or more columns
4

It is very important clause
5

I wanted to pay attention as this will be used a lot many times in your professional career
6

To use group by this is a syntax
7

We write select
8

Give the column names
9

We can use the aggregate functions also
10

From the table name
11

And then write group by a particular column name
12

So what this mean is for example if you want to find out
13

The number of customers who are living in a particular City or in a particular state
14

You will then be grouping the customers by their city name or the state
15

So if you are grouping them by city you will write group by
16

City
17

If you are grouping them by state you will write group by state and you will select the respective
18

Column with respective aggregate functions. Let us see some examples
19

So in the first example
20

Suppose you want to find out
21

How many customers live in which region of the country
22

To Do that
23

We'll select the region
24

And the count of customer ID
25

And this we will do from the table customer
26

And will group this by the column region
27

So if you can imagine this will group the regions
28

Will get the name of the regions
29

And alongside that, it will give us the count of customer ID in that region
30

Let us go and write this query
31

If you scroll this customer table
32

The last column is the region column in which we have different regions
33

Let us
34

Find out the number of customers in each region using the group by clause
35

So
36

We will select
37

The region
38

This will give us the region names then will write the count
39

Of customer IDs
40

This will give us the number of customers, I'll give an alias of
41

Customer count
42

From a Table name which is customer and I will group it by
43

The region column
44

Run this query
45

It ran successfully and we have the result, we have four regions
46

And we have the count of customers in each region
47

We have 134 customers in South region and 255 customers in West region
48

So this was using group by along with the count aggregate function
49

Now let us look at
50

How to use group by along with the
51

Sum aggregate function
52

Suppose we want to find out the quantity of each product sold
53

From the sales table
54

How do we do that
55

To Find out we will write select product ID
56

And the we will have the aggregate function of sum
57

Which is summing the quantity of that product ID sold
58

From the sales table
59

Grouped by their product ID
60

And ordered in the descending order
61

One thing to notice
62

When I am ordering it in descending order, I am using the alias
63

Once alias is defined it can be used further in the query wherever you want to
64

Now let us write this query to find out quantity of each product sold select
65

Product ID
66

, Sum quantity
67

Give it an alias as quantity sold
68

From
69

Sales table
70

Grouped by
71

group by product ID
72

And ordered by
73

Quantity sold
74

In descending order so we will get
75

The maximum sold product at the top
76

So this is the product ID of the maximum sold product, it was sold 75 times
77

Next
78

Product was sold 70 times and so on
79

IN the next example let us see how to use all the aggregate functions that we have learnt before in one single query
80

And along with the group by clause
81

Suppose we are creating a dashboard and trying to find out
82

The top five customers who has spent most at our Store
83

And Get an idea of what is their profile, how much they have spent
84

What is the average spend
85

What is the minimum sales value and what is the maximum sales value
86

To do this
87

We will write select customer ID
88

Minimum sales value
89

And then write the max sales value
90

We'll find out the average sales value
91

And the sum that is the total sales value
92

From the sales table and group it by the customer ID and will order them in descending order
93

We will put a limit of 5 so that the top 5 spenders only remain in the resultant set
94

Let us go and run this query
95

Select
96

Customer ID
97

And
98

Find the minimum of the
99

Sales
100

As Minimum sales
101

We'll find the max sales as maximum sale
102

The average sales
103

As Average
104

Underscore sale
105

Then the sum of all sales at the total sales
106

Space
107

Total sales
108

From the sales table, group by
109

Customer ID
110

Ordered by total sales
111

This is the alias given to sum sales
112

And in the descending order
113

And we will limit the number of records to 5
114

So we want the top 5 spenders only
115

You can see when I run this query I get top
116

5 spenders
117

The topper is the one who has spent
118

25,043 units
119

Average spend of this customer is 16069
120

Minimum spend was 3.4 and maximum is
121

22638
122

So you can present this Dashboard of the highest spenders of your Store
123

One thing you should note here is
124

The columns that we are specifying
125

While selecting
126

these should be unique
127

with the
128

Column mentioned in the group by clause
129

What I mean by this is
130

For example
131

When you are selecting the count of customers with the region
132

If I had put region, age
133

And group by region
134

It could not have been
135

Group together since there are several types of Ages of customers
136

Within same region, So let us try and run this query
137

And see the error
138

SQL gives
139

It is saying column customer. age must appear in group by clause or be used in an aggregate function
140

What this means is
141

What this means is either you can use the age as an aggregate function
142

You can find the average age
143

Or the minimum or maximum age
144

You cannot use it
145

As it is
146

Since it is not unique in one particular region
147

So If you want to find out average age
148

Probably we should have an aggregate function
149

So we should write average
150

Age also here and run this query
151

Now
152

We have the
153

Now
154

If I expand this column of age
155

The Average age in South is 45
156

Whereas
157

In All the other regions it is nearly 44
158

So Remember to use aggregate functions along with group by
159

Another important thing to note
160

We have used only one column
161

While specifying group by clause, you can also specify multiple columns
162

So if I specify two different columns, it will create all the possible combinations of values in
163

Both the columns
164

And
165

It will give us a table of all the possible combinations. So let us add
166

this region
167

Let us add the state also
168

And we will group it by region and state
169

Let us run this query
170

So you can see
171

The maximum customer count
172

Is in the east region
173

Of the New York State
174

Next is the Texas state
175

In the Central region
176

So, It is creating groups of all the possible combinations of
177

Regions and States
178

In the next lecture, we will discuss the having keyword