Inicio‎ > ‎SQL‎ > ‎SQL (english)‎ > ‎

T06 Aggregation filtering

Having

Once the calculated information over the groups of rows is obtained using the group by, it is possible to filter these results and showing only those we need using the having clause. Having is to group by the same as where is to select-from

Likewise the where clause is used to select rows, the having clause is used to select groups; if in the where the condition specified affects to the tuples of the whole table, the group by perfoms calculus over this previous selection and returns as a result a table with all the calculated information for each group of that selection. Over this last, the having clause will eliminate those tuples that do not fulfull the condition.

Where filters the data stored in a table and having the calculated information.

We can understand the process of a query with group by-having as a previous execution of the  select-from-where part and, from this intermediate result, we obtain the calculated information with group by and, finally, the filtering with having.

How many subjects teach each teacher?

select p.dni, nombre, count(*) asignaturas
from profesores p, imparte i
where p.dni = i.dni
group by p.dni, nombre

dni nombre asignaturas
21111222 EVA GOMEZ 2
21333444 RAFAEL ROMERO 1




How many subjects are taught by teachers with 2 or more subjects?

select p.dni, nombre, count(*) asignaturas
from profesores p, imparte i
where p.dni = i.dni
group by p.dni, nombre
having count(*) >= 2

dni nombre asignaturas
21111222 EVA GOMEZ 2



Having does not allow using the tag of the calculated columns, like in this case "asignaturas", neither the order of the column, that it would be interpreted as an integer number.


Without considering the FBD subject, how many subjects are taught by teachers with 2 or more subjects?

select p.dni, nombre, count(*) asignaturas
from profesores p, imparte i
where p.dni = i.dni
and asignatura != 'FBD'
group by p.dni, nombre
having count(*) >= 2

Ninguna fila seleccionada


Step by step, the previous query:
  1. where has eliminated the tuples of imparte with subject's code as FBD.
  2. group by calculates, for each teacher, the number of subjects he/she teaches. Due to the fact that FBD is not included, Eva Gómez only teach, as wll as Rafael Romero, one only subject.
  3. having eliminates from the result of the previous step all those tuples with a value in the count of the rows less than 2. The result is empty due to the fact that no rows fulfill the condition.

The condition of the having clause is not necessarily over the calculus to show, as it can be seen in the next example.


How many subjects teach every teacher in the case that the sum of the credits are greater than 5?

select p.dni, nombre, count(*) asignaturas
from profesores p, imparte i, asignaturas a
where p.dni = i.dni and a.codigo = i.asignatura
group by p.dni, nombre
having sum(creditos) > 5

dni nombre asignaturas
21111222 EVA GOMEZ 2
21333444 RAFAEL ROMERO 1


MySQL allows to move away from SQL standard

We have to be careful with a feature of MySQL that also depends on the configuration of the server. The issue is that it is possible that MySQL accepts the following order in which we have omitted the column "nombre" from the group by:

DNI and name of the coordinator and how many subjects he/she coordinates

select dni, nombre, count(*) coordina
from asignaturas a, coordinadores c
where c.asig = a.codigo
group by dni;


In this case, there is no problem because in the case that for each dni there is just one name, dni is the identifier of the coordinators, but this is not always the case. As in many other things, MySQL offers its own functionalities that can be chosen or not, but moving away from the standard makes more difficult the migration to others DBMS.

MySQL leaves in hands of the one doing the query taking care of the criteria for the grouping, making sure that the omitted columns would not alter the result. It is possible to get more info at MySQL manual. There are authors in favour to "move away" from the standard, like Roland Bouman.

Another feature of MySQL is that allows using having by default without group by:

select * from asignaturas
having creditos > 3;


The idea of this "permissiveness" is that where does not allow to use agregation functions whereas having does. Again, this MySQL extension is not accepted by all the DBMS.

In any case, we do not recommend these ways of querying, or at least not in a basic learning context, neither will be accepted in our evaluated exercises.
Comments