Original subtitles

0 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