DML: GROUP BY

name: portada
layout: true
class: portada-slide, middle, right
---
# DML: GROUP BY
## Databases

.footnote[Joan Puigcerver Ibáñez ([j.puigcerveribanez@edu.gva.es](mailto:j.puigcerveribanez@edu.gva.es))]

---
layout: true
class: regular-slide
.right[.logo[![logoMislata](/itb/images/logo_mislata.png)]]

---
# GROUP BY
The GROUP BY clause groups rows together that have the same value in one or more column in a single row.

__Agreggate functions__ can be used in combination with the GROUP BY clause.

.center[[GROUP BY MariaDB](https://mariadb.com/kb/en/group-by/)]

```sql
SELECT 
  [ALL | DISTINCT | DISTINCTROW]
  column_expr [, column2_expr ...]
  FROM table
    [WHERE condition]
    [ORDER BY {col_name | expr | position} [ASC | DESC], ...]
    [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING aggregate_condition]
```

---
# GROUP BY example

- Table __enrolled__:

.columns[
.column[
| subject | studentDNI | grade |
| :-: | :-: | :-: |
| DBA | 00000000A | 5.6 |
| DBA | 00000001B | 8.0 |
| DBA | 00000003D | 4.3 |
| PRG | 00000000A | 7.1 |
| PRG | 00000001B | 9.2 |
| PRG | 00000003D | 6.1 |
]

.column[
```sql
SELECT subject, studentDNI, grade
FROM enrolled;
```
```text
+---------+------------+-------+
| subject | studentDNI | grade |
+---------+------------+-------+
| DBA     | 00000000A  |   5.6 |
| DBA     | 00000001B  |   8.0 |
| DBA     | 00000003D  |   4.3 |
| PRG     | 00000000A  |   7.1 |
| PRG     | 00000001B  |   9.2 |
| PRG     | 00000003D  |   6.1 |
+---------+------------+-------+
```
]
]

---
# Example GROUP BY: Subject

In this example, the data has been grouped by the __subject__ column. Also, the aggregate function AVG has been used in the grades of the students enrolled in the same subject.

.columns[
.column[
<table>
  <thead>
    <tr>
      <th>subject</th>
      <th>studentDNI</th>
      <th>grade</th>
    </tr>
  </thead>
  <tbody>
    <tr class="gold">
      <td><strong>DBA</strong></td>
      <td>00000000A</td>
      <td>5.6</td>
    </tr>
    <tr class="gold">
      <td><strong>DBA</strong></td>
      <td>00000001B</td>
      <td>8.0</td>
    </tr>
    <tr class="gold">
      <td><strong>DBA</strong></td>
      <td>00000003D</td>
      <td>4.3</td>
    </tr>
    <tr class="green">
      <td><strong>PRG</strong></td>
      <td>00000000A</td>
      <td>7.1</td>
    </tr>
    <tr class="green">
      <td><strong>PRG</strong></td>
      <td>00000001B</td>
      <td>9.2</td>
    </tr>
    <tr class="green">
      <td><strong>PRG</strong></td>
      <td>00000003D</td>
      <td>6.1</td>
    </tr>
  </tbody>
</table>
]

.column[
```sql
SELECT subject, AVG(grade)
FROM enrolled
GROUP BY subject;
```
```text
+---------+------------+
| subject | AVG(grade) |
+---------+------------+
| DBA     |       5.96 |
| PRG     |       7.46 |
+---------+------------+
```
]
]

---
# Example GROUP BY: Student

In this example, the data has been grouped by __studentDNI__ column. Also, the aggregate function AVG has been used in the grades of each student enrolled in multiple subjects.

.columns[
.column[
<table>
  <thead>
    <tr>
      <th>subject</th>
      <th>studentDNI</th>
      <th>grade</th>
    </tr>
  </thead>
  <tbody>
    <tr class="gold">
      <td><strong>DBA</strong></td>
      <td>00000000A</td>
      <td>5.6</td>
    </tr>
    <tr class="green">
      <td><strong>DBA</strong></td>
      <td>00000001B</td>
      <td>8.0</td>
    </tr>
    <tr>
      <td><strong>DBA</strong></td>
      <td>00000003D</td>
      <td>4.3</td>
    </tr>
    <tr class="gold">
      <td><strong>PRG</strong></td>
      <td>00000000A</td>
      <td>7.1</td>
    </tr>
    <tr class="green">
      <td><strong>PRG</strong></td>
      <td>00000001B</td>
      <td>9.2</td>
    </tr>
    <tr class=>
      <td><strong>PRG</strong></td>
      <td>00000003D</td>
      <td>6.1</td>
    </tr>
  </tbody>
</table>
]

.column[
```sql
SELECT studentDNI, AVG(grade)
FROM enrolled
GROUP BY studentDNI;
```
```text
+------------+------------+
| studentDNI | AVG(grade) |
+------------+------------+
| 00000000A  |       6.35 |
| 00000001B  |        8.6 |
| 00000003D  |        5.2 |
+------------+------------+
```
]
]

---
# HAVING
The __HAVING__ clause is used to select the rows that mat a specific condition using aggregate functions.

From the previous example, now only the students with an grade average greater than 7.5 will be selected.

.columns[
.column[
<table>
  <thead>
    <tr>
      <th>subject</th>
      <th>studentDNI</th>
      <th>grade</th>
    </tr>
  </thead>
  <tbody>
    <tr class="gold">
      <td><strong>DBA</strong></td>
      <td>00000000A</td>
      <td>5.6</td>
    </tr>
    <tr class="green">
      <td><strong>DBA</strong></td>
      <td>00000001B</td>
      <td>8.0</td>
    </tr>
    <tr>
      <td><strong>DBA</strong></td>
      <td>00000003D</td>
      <td>4.3</td>
    </tr>
    <tr class="gold">
      <td><strong>PRG</strong></td>
      <td>00000000A</td>
      <td>7.1</td>
    </tr>
    <tr class="green">
      <td><strong>PRG</strong></td>
      <td>00000001B</td>
      <td>9.2</td>
    </tr>
    <tr class=>
      <td><strong>PRG</strong></td>
      <td>00000003D</td>
      <td>6.1</td>
    </tr>
  </tbody>
</table>
]

.column[
```sql
SELECT studentDNI, AVG(grade)
FROM enrolled
GROUP BY studentDNI
HAVING AVG(grade) >= 7.5;
```
```text
+------------+------------+
| studentDNI | AVG(grade) |
+------------+------------+
| 00000001B  |        8.6 |
+------------+------------+
```
]
]