Embedded Files
Group by
Group by
Remember that the use of aggregating functions allows us to perform calculus over the all the rows that fulfill a certain condition (or over all the rows of the table).
Number of credits being teach
select sum(creditos) credsImp
from imparte i, asignaturas a
where i.asignatura = a.codigo
However, we usually need to perform this operation not in a global way, but depending on a particular criteria.
Number of credits taught by each teacher.
select sum(creditos) credsImp
from imparte i, asignaturas a
where i.asignatura = a.codigo
group by dni
Actually, it would be better understood if we introduce more information in the output
select dni, sum(creditos) credsImp
from imparte i, asignaturas a
where i.asignatura = a.codigo
group by dni
The criteria defined by group by (dni) establishs the different groups of rows in which the sum will be performed. The system looks for each different dni values within table imparte, and for all the rows sharing these values performs the sum of credits.
From there, we can add any information we consider important.
Name of the teachers and credits they teach.
select nombre, sum(creditos) credsPorProf
from imparte i, asignaturas a, profesores p
where i.asignatura = a.codigo
and p.dni = i.dni
group by p.dni
The previous error is due to the fact that is compulsory adding to the group by all the no calculated columns that will appear in the final result.
SQL Error: Column 'ejemplo.p.nombre' isn't in GROUP BY
On the contrary, it is not necessary that all the columns specified in the group by will appear in the output.
select nombre, sum(creditos) credsPorProf
from imparte i, asignaturas a, profesores p
where i.asignatura = a.codigo
and p.dni = i.dni
group by p.dni, nombre
We have also grouped by "DNI" for prevention. Although in one state of the database the results are the same —in this one, all teachers have different names— in another state it could happen that two teachers, two different DNIs, share the same name.
Note that we are starting from a multi-table query and that the result will, predictably, show the same teacher in several rows. However, in a query using only one table, the use of group by might be unnecessary.
select codigo, sum(creditos) from asignaturas group by codigo
You would get the same result as
select codigo, creditos from asignaturas
We are trying to group by subject code, the primary key of the single table we are querying. Therefore, there is nothing to group, each row represents a different subject, all the sums are of a single value, we can't even call them "sums". This does not mean that information cannot be aggregated in a single table, but simply that the grouping criteria chosen should be different.
Where and group by
Where and group by
When the clause where is used, apart from linking tables by common columns, as PROFESORES and IMPARTE using profesores.dni and imparte.dni respectively, it is also possible to use it to eliminate certain rows from the calculus.
Therefore, if we want to calculate how many subjects are taught by each teacher without taking into account FBD, we will write the following sentence:
Except for 'FBD', calculate how many subjects each teacher teaches.
select nombre, count(*) coordina
from asignaturas a, coordinadores c
where c.asig = a.codigo
group by nombre;
The steps are, "first" the from-where sentence is performed with no calculus, and "then", over the resulted rows, the calculus will be performed following the group by criteria. More precisely:
from
where
group by
select
No key attributes
No key attributes
We have been using the primary key of teachers in the group by clauses in order to prevent erroneous operations. The only attributes that guarantee the identification of a tuple respect the others are the ones in the primary key (in general, candidate key).
Therefor, if the primary key of a people table (e.g teachers or students) is D.N.I., we assume that there is no duplicate values for this attribute. However, the name is not a key attribute, and therefore, admits duplicates. That is, it is perfectly possible to find to people with the same name.
Suppose that the EJEMPLO database contains a SUPERVISORES table (supervisors) and another SUPERVISA table (coordinates):
CREATE TABLE supervisores (
dni VARCHAR(10),
nombre VARCHAR(40),
PRIMARY KEY (dni)
);
CREATE TABLE supervisa (
dni VARCHAR(10),
asig CHAR(5),
PRIMARY KEY (dni,asig),
FOREIGN KEY (asig) REFERENCES asignaturas (codigo)
);
Name of the coordinator and how many subjects he/she coordinates.
select s.nombre supervisor,a.descripcion asignatura
from supervisores s, supervisa r, asignaturas a
where s.dni=r.dni and r.asig=a.codigo;
It might seem that Agapito Cifuentes supervises 3 subjects, but
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, nombre;
The truth is that there are two different people who happen to have the same name and surname. One supervises two subjects and the other only one. Obviously, this affects the aggregation.
select s.nombre supervisor,count(*) cuantas
from supervisores s, supervisa r, asignaturas a
where s.dni=r.dni and r.asig=a.codigo
group by s.nombre;
select s.dni,s.nombre supervisor,count(*) cuantas
from supervisores s, supervisa r, asignaturas a
where s.dni=r.dni and r.asig=a.codigo
group by s.dni,s.nombre;
Output ordering
Output ordering
It is possible to order the output using the calculated columns, , including by other aggregate functions not present in the select.
All these statements have the same output:
Using the expression itself
select nombre, count( * ) asignaturas
from profesores p, imparte i
where p.dni=i.dni
group by p.dni, nombre
order by count( * );
Using the column alias
select nombre, count( * ) asignaturas
from profesores p, imparte i
where p.dni=i.dni
group by p.dni, nombre
order by asignaturas;
Using the ordering of the column
select nombre, count( * ) asignaturas
from profesores p, imparte i
where p.dni=i.dni
group by p.dni, nombre
order by 2;
Page updated
Google Sites
Report abuse