All language subtitles for 7. LIST AGGREGATION

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