# Oracle - Group Functions

### Oracle Group Functions

#### Why do we need Group Functions?

Take the below table as example:

![](https://i.imgur.com/ImfWEV3.png)

You may want to group them by the `STATE` .

```sql
select state from customers group by state order by state;
```

![](https://i.imgur.com/rqY42ct.png)

As you can see, in the previous table, we have 3 rows of state `CA`. But in the below table, we only have 1.

It means that, when we are doing `group by` , some data just get lost. So if you are trying to do like this:

```sql
select firstname, state from customers group by state order by state;
```

Error could happen:

```
ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"
*Cause:    
*Action:
Error at Line: 73 Column: 8
```

To explain what's going on here in a relatively simple way, we can say that as you may lose data when you are doing group by if you select other column other than the grouped column `STATE`. Oracle do not like this and also it cannot judge which data to lose.

That's why you are going to use group function to "Aggregate" them together.

```sql
select count(*) "count", state from customers group by state order by state;
```

There are so many Group Functions mentioned in the lab material, I'm not going to run all of them. But I do love to share one function I loved.

```sql
select listagg(firstname || ', ' || lastname) "All Customers", state from customers group by state order by state;
```

![](https://i.imgur.com/kn6LdSi.png)

As you can see, this is to combine all the names in the group so that you lose no data.

#### Grouping Sets

> <https://www.oracletutorial.com/oracle-basics/oracle-grouping-sets/>

We are going to analyze what the below statement is doing:

```sql
select name, category, count(isbn), to_char(avg(retail), '99.99') "AVG RETAIL"
from publisher join books using (pubid)
where pubid in (2,3,5)
group by grouping sets (name, category, (name, category),());
```

Firstly, we may want to see the output:

![](https://i.imgur.com/4XpKdIx.png)

It looks weird, and then we are going to do some analysis:

Firstly, what are we unfamiliar with?

```sql
group by grouping sets (name, category, (name, category),());
```

So we just remove them and try to run the rest:

```sql
select name, category "AVG RETAIL"
from publisher join books using (pubid)
where pubid in (2,3,5);
```

![](https://i.imgur.com/KH0Ve9v.png)

Here we got something related to the previous image. This is to show all the `name` and `category`.

So what if we run something else?

```sql
select name, category, count(isbn), to_char(avg(retail), '99.99') "AVG RETAIL"
from publisher join books using (pubid)
where pubid in (2,3,5)
group by grouping sets (name, category);
```

And we get:

![](https://i.imgur.com/5vkXdeU.png)

It looks like the `grouping sets (name, category)` is going to combine the result of `group by name` and `group by category`

If we keep running like this, we can find that do is to combine the group by result together by adding all the rows.

To be mentioned, we will get the below output when we run:

`group by name, category`

![](https://i.imgur.com/4G5h0wN.png)

compare with the original data, we can find this means we are doing something like "adding name and category together to be a new attribute and use that to group the result".

![](https://i.imgur.com/FmVeMig.png)

Another thing is the `()` in the `groupping sets`,

> A grouping sets may include zero column. In this case, it is an empty grouping set, which is denoted by `()`.\[^1]

#### Cube

> In addition to the subtotals generated by the `ROLLUP` extension, the `CUBE` extension will generate subtotals for all combinations of the dimensions specified. If "n" is the number of columns listed in the `CUBE`, there will be 2n subtotal combinations.\[^2]

This means that find out all the possible combinations with the given group attributes.

So the below statements works the same way:

```sql
select name, category, count(isbn), to_char(avg(retail), '99.99') "AVG RETAIL"
from publisher join books using (pubid)
where pubid in (2,3,5)
group by grouping sets (name, category, (name, category),());

select name, category, count(isbn), to_char(avg(retail), '99.99') "AVG RETAIL"
from publisher join books using (pubid)
where pubid in (2,3,5)
group by cube (name, category);
```

The advantage of `groupping sets` can be it doesn't necessarily take all possible combinations of the columns.

#### Rollup

```sql
select name, category, count(isbn), to_char(avg(retail), '99.99') "AVG RETAIL"
from publisher join books using (pubid)
where pubid in (2,3,5)
group by rollup (name, category)
order by name, category;
```

Below we see the output:

![](https://i.imgur.com/FemHhHu.png)

If you find difficult to understand what `rollup` is doing, you can also run this:

```sql
select name, count(isbn), to_char(avg(retail), '99.99') "AVG RETAIL"
from publisher join books using (pubid)
where pubid in (2,3,5)
group by name
order by name;
```

![](https://i.imgur.com/mLPwZ7e.png)

See the similarity?

`rollup` is going to calculate an extra group result for the first column to be grouped.

> You can check this link for more detailed explanation: <https://www.oracletutorial.com/oracle-basics/oracle-rollup/>

### Reference:

1. <https://www.oracletutorial.com/oracle-basics/oracle-grouping-sets/>
2. <https://oracle-base.com/articles/misc/rollup-cube-grouping-functions-and-grouping-sets#cube>
