Original subtitles

0 The next and the last function is a string aggregator 1

String aggregator will concatenate all the input values into a single string 2

And all those values will be 3

Separated by a delimiter delimiter is a symbol which you will mention and this symbol will segregate 4

the input values of the string 5

The syntax for 6

String aggregation is string underscore 7

agg 8

and then you mention the expression 9

This will contain the strings or it will contain the column name 10

Comma 11

The delimiter, delimiter usually is a comma 12

You can have space 13

We have tabs you can have any other special symbol 14

Note that 15

String aggregator is also concatenating strings 16

But what is the difference between a string aggregator and the CONCAT operator 17

When we used concat 18

It 19

Concatenated strings 20

Of different column for a single record 21

For example We concatenated 22

City state and country for each customer 23

Where as string aggregator will be used 24

To concatenate all values in a column 25

So for different records 26

There will be different values in that column 27

All those different values can be aggregated 28

Using this string aggregate function 29

Let us look at this using example 30

If you remember from the sales table 31

In the sales table there are different rows for each order 32

And each order had different products 33

So with the same order ID 34

There'll be multiple rows 35

And in these different rows there will be different product IDs. Basically the customer has ordered within a single order 36

Different products 37

If you want to find out 38

What are the products in each order ID 39

So we willl first group the order by order ID 40

And within every order ID 41

We will aggregate the different product IDs 42

And we'll separate these with a 43

Comma 44

This is the 45

Syntax select order ID, string aggregators 46

The product ID separated by comma from sales table group by order ID 47

Let's go and see in the pg Admin. Let us first do a select star on the sales table 48

Just to 49

So that I show you there are different order IDs and product IDs which 50

are taking up different rows in the table 51

You can see 52

this second and third row 53

The order ID is same 54

But this row is different because the product ID is different 55

So I want to find out 56

For each order ID 57

What are the products 58

Ordered. To do that 59

I'll group 60

it by the order ID 61

And against each order ID I'll have the product IDs concatenated 62

Let's see. select order ID 63

And 64

String underscore agg 65

This 66

Column is to be aggregated separated by comma space 67

Comma space 68

From the sales table, grouped by 69

The order ID 70

Let's order it also by the order ID 71

And run it 72

You can see in the sales table there were multiple entries of the same order ID 73

here we have grouped it so it will be a single entry for each order ID 74

Add against that order ID you will see the different 75

Products 76

So this second order ID 77

Has 78

Two products separated by a comma 79

If you go down you will find 80

several order IDs with 81

More than two products also 82

So you can see we have concatenated values in different rows 83

Of the same column the column was product ID 84

So we have concatenated values in different rows 85

So this is what string aggregator function does 86

That is all for the string functions 87

In the next section we will look at Mathematical functions