35 - Agrupar registros (group by)

Hemos aprendido que las funciones de agrupamiento permiten contar registros, calcular sumas y promedios, obtener valores máximos y mínimos. También dijimos que dichas funciones operan sobre conjuntos de registros, no con datos individuales.

Generalmente esta funciones se combinan con la sentencia "group by", que agrupa registros para consultas detalladas.

Queremos saber la cantidad de visitantes de cada ciudad, podemos tipear la siguiente sentencia:

select count(*) from visitantes
  where ciudad='Cordoba';

y repetirla con cada valor de "ciudad":

select count(*) from visitantes
  where ciudad='Alta Gracia';
 select count(*) from visitantes
  where ciudad='Villa Dolores';
...

Pero hay otra manera, utilizando la cláusula "group by":

select ciudad, count(*)
  from visitantes
  group by ciudad;

Entonces, para saber la cantidad de visitantes que tenemos en cada ciudad utilizamos la función "count()", agregamos "group by" y el campo por el que deseamos que se realice el agrupamiento, también colocamos el nombre del campo a recuperar.

La instrucción anterior solicita que muestre el nombre de la ciudad y cuente la cantidad agrupando los registros por el campo "ciudad". Como resultado aparecen los nombres de las ciudades y la cantidad de registros para cada valor del campo.

Para obtener la cantidad visitantes con teléfono no nulo, de cada ciudad utilizamos la función "count()" enviándole como argumento el campo "telefono", agregamos "group by" y el campo por el que deseamos que se realice el agrupamiento (ciudad):

select ciudad, count(telefono)
  from visitantes
  group by ciudad;

Como resultado aparecen los nombres de las ciudades y la cantidad de registros de cada una, sin contar los que tienen teléfono nulo. Recuerde la diferencia de los valores que retorna la función "count()" cuando enviamos como argumento un asterisco o el nombre de un campo: en el primer caso cuenta todos los registros incluyendo los que tienen valor nulo, en el segundo, los registros en los cuales el campo especificado es no nulo.

Para conocer el total de las compras agrupadas por sexo:

select sexo, sum(montocompra)
  from visitantes
  group by sexo;

Para saber el máximo y mínimo valor de compra agrupados por sexo:

select sexo, max(montocompra) from visitantes
  group by sexo;
 select sexo, min(montocompra) from visitantes
  group by sexo;

Se pueden simplificar las 2 sentencias anteriores en una sola sentencia, ya que usan el mismo "group by":

select sexo, max(montocompra),
  min(montocompra)
  from visitantes
  group by sexo;

Para calcular el promedio del valor de compra agrupados por ciudad:

select ciudad, avg(montocompra) from visitantes
  group by ciudad;

Podemos agrupar por más de un campo, por ejemplo, vamos a hacerlo por "ciudad" y "sexo":

select ciudad, sexo, count(*) from visitantes
  group by ciudad,sexo;

También es posible limitar la consulta con "where".

Vamos a contar y agrupar por ciudad sin tener en cuenta "Cordoba":

select ciudad, count(*) from visitantes
  where ciudad<>'Cordoba'
  group by ciudad;

Podemos usar las palabras claves "asc" y "desc" para una salida ordenada:

select ciudad, count(*) from visitantes
  group by ciudad desc;

Problema Resuleto:

Un comercio que tiene un stand en una feria registra en una tabla llamada "visitantes" algunos datos de las personas que visitan o compran en su stand para luego enviarle publicidad de sus productos.

Eliminamos la tabla, si existe:

drop table if exists visitantes;

Creamos la tabla:

create table visitantes(
  nombre varchar(30),
  edad tinyint unsigned,
  sexo char(1),
  domicilio varchar(30),
  ciudad varchar(20),
  telefono varchar(11),
  montocompra decimal (6,2) unsigned
 );

Ingresamos algunos registros:

insert into visitantes (nombre,edad, sexo,domicilio,ciudad,telefono,montocompra)
  values ('Susana Molina', 28,'f','Colon 123','Cordoba',null,45.50); 
 insert into visitantes (nombre,edad, sexo,domicilio,ciudad,telefono,montocompra)
  values ('Marcela Mercado',36,'f','Avellaneda 345','Cordoba','4545454',0);
 insert into visitantes (nombre,edad, sexo,domicilio,ciudad,telefono,montocompra)
  values ('Alberto Garcia',35,'m','Gral. Paz 123','Alta Gracia','03547123456',25); 
 insert into visitantes (nombre,edad, sexo,domicilio,ciudad,telefono,montocompra)
  values ('Teresa Garcia',33,'f','Gral. Paz 123','Alta Gracia','03547123456',0);
 insert into visitantes (nombre,edad, sexo,domicilio,ciudad,telefono,montocompra)
  values ('Roberto Perez',45,'m','Urquiza 335','Cordoba','4123456',33.20);
 insert into visitantes (nombre,edad, sexo,domicilio,ciudad,telefono,montocompra)
  values ('Marina Torres',22,'f','Colon 222','Villa Dolores','03544112233',25);
 insert into visitantes (nombre,edad, sexo,domicilio,ciudad,telefono,montocompra)
  values ('Julieta Gomez',24,'f','San Martin 333','Alta Gracia','03547121212',53.50);
 insert into visitantes (nombre,edad, sexo,domicilio,ciudad,telefono,montocompra)
  values ('Roxana Lopez',20,'f','Triunvirato 345','Alta Gracia',null,0);
 insert into visitantes (nombre,edad, sexo,domicilio,ciudad,telefono,montocompra)
  values ('Liliana Garcia',50,'f','Paso 999','Cordoba','4588778',48);
 insert into visitantes (nombre,edad, sexo,domicilio,ciudad,telefono,montocompra)
  values ('Juan Torres',43,'m','Sarmiento 876','Cordoba','4988778',15.30);

Para saber la cantidad de visitantes que tenemos de cada ciudad tipeamos:

select ciudad, count(*)
  from visitantes
  group by ciudad;

El resultado muestra los nombres de las distintas ciudades y la cantidad de registros de cada una.
Necesitamos conocer la cantidad visitantes con teléfono no nulo, de cada ciudad:

select ciudad, count(telefono)
 from visitantes
  group by ciudad;

Queremos conocer el total de las compras agrupadas por sexo:

select sexo, sum(montocompra) from visitantes
  group by sexo;

Para obtener el máximo y mínimo valor de compra agrupados por sexo:

select sexo, max(montocompra) from visitantes
  group by sexo;
 select sexo, min(montocompra) from visitantes
  group by sexo;

Se pueden simplificar las 2 sentencias anteriores en una sola sentencia, ya que usan el mismo "group by":

select sexo, max(montocompra),
  min(montocompra)
  from visitantes
  group by sexo;

Queremos saber el promedio del valor de compra agrupados por ciudad:

select ciudad, avg(montocompra) from visitantes
  group by ciudad;

Contamos los registros y agrupamos por 2 campos, "ciudad" y "sexo":

select ciudad, sexo, count(*) from visitantes
  group by ciudad,sexo;

Limitamos la consulta, no incluimos los visitantes de "Cordoba", contamos y agrupar por ciudad:

select ciudad, count(*) from visitantes
  where ciudad<>'Cordoba'
  group by ciudad;

Usando la palabra clave "desc" obtenemos la salida ordenada en forma descendente:

select ciudad, count(*) from visitantes
  group by ciudad desc;

Problema Propuesto:

 
Una empresa tiene registrados sus clientes en una tabla llamada "clientes".

1- Elimine la tabla "clientes", si existe.

2- Créela con la siguiente estructura:

 create table clientes (
  codigo int unsigned auto_increment,
  nombre varchar(30) not null,
  domicilio varchar(30),
  ciudad varchar(20),
  provincia varchar (20),
  telefono varchar(11),
  primary key(codigo)
 );

3- Ingrese algunos registros:

 insert into clientes (nombre,domicilio,ciudad,provincia,telefono)
  values ('Lopez Marcos', 'Colon 111', 'Córdoba','Cordoba','null');
 insert into clientes (nombre,domicilio,ciudad,provincia,telefono)
  values ('Perez Ana', 'San Martin 222', 'Cruz del Eje','Cordoba',

'4578585');
 insert into clientes (nombre,domicilio,ciudad,provincia,telefono)
  values ('Garcia Juan', 'Rivadavia 333', 'Villa Maria','Cordoba',

'4578445');
 insert into clientes (nombre,domicilio,ciudad,provincia,telefono)
  values ('Perez Luis', 'Sarmiento 444', 'Rosario','Santa Fe',null);
 insert into clientes (nombre,domicilio,ciudad,provincia,telefono) 
  values ('Pereyra Lucas', 'San Martin 555', 'Cruz del Eje','Cordoba',

'4253685');
 insert into clientes (nombre,domicilio,ciudad,provincia,telefono)
  values ('Gomez Ines', 'San Martin 666', 'Santa Fe','Santa Fe','0345252525');
 insert into clientes (nombre,domicilio,ciudad,provincia,telefono)
  values ('Torres Fabiola', 'Alem 777', 'Villa del Rosario','Cordoba',

'4554455');
 insert into clientes (nombre,domicilio,ciudad,provincia,telefono)
  values ('Lopez Carlos', 'Irigoyen 888', 'Cruz del Eje','Cordoba',null);
 insert into clientes (nombre,domicilio,ciudad,provincia,telefono)
  values ('Ramos Betina', 'San Martin 999', 'Cordoba','Cordoba','4223366');
 insert into clientes (nombre,domicilio,ciudad,provincia,telefono)
  values ('Lopez Lucas', 'San Martin 1010', 'Posadas','Misiones',

'0457858745');

4- Obtenga el total de los registros (10):
 
5- Obtenga el total de los registros que no tienen valor nulo en los 

teléfonos (8):
 
6- Obtenga la cantidad de clientes agrupados por ciudad y provincia, 

ordenados por provincia: 

Otros problemas: 

A) En una página web se solicitan los siguientes datos para 

guardar información de sus visitas.

1- Elimine la tabla "visitas", si existe.

2- Créela con la siguiente estructura:

 create table visitas (
  numero int unsigned auto_increment,
  nombre varchar(30) not null,
  mail varchar(50),
  pais varchar (20),
  fecha date,
  primary key(numero)
);

3- Ingrese algunos registros:

 insert into visitas (nombre,mail,fecha)
  values ('Ana Maria Lopez','AnaMaria@hotmail.com','2006-10-10');
 insert into visitas (nombre,mail,fecha)
  values ('Gustavo Gonzalez','GustavoGGonzalez@hotmail.com','2006-10-10');
 insert into visitas (nombre,mail,fecha)
  values ('Juancito','JuanJosePerez@hotmail.com','2006-10-11');
 insert into visitas (nombre,mail,fecha)
  values ('Fabiola Martinez','MartinezFabiola@hotmail.com','2006-10-12');
 insert into visitas (nombre,mail,fecha)
  values ('Fabiola Martinez','MartinezFabiola@hotmail.com','2006-09-12');
 insert into visitas (nombre,mail,fecha)
  values ('Juancito','JuanJosePerez@hotmail.com','2006-09-12');
 insert into visitas (nombre,mail,fecha)
  values ('Juancito','JuanJosePerez@hotmail.com','2006-09-15');

4- Obtenga el total de visitas.

5- Cantidad de visitas agrupadas por fecha:
 
6- Cantidad de visitas agrupadas por nombre y mes:
 


B) Una empresa registra los datos de sus empleados en una tabla 

llamada "empleados".

1- Elimine la tabla "empleados" si existe:
 
2- Cree la tabla:

 create table empleados(
  documento char(8) not null,
  nombre varchar(30) not null,
  sexo char(1),
  domicilio varchar(30),
  fechaingreso date,
  fechanacimiento date,
  sueldobasico decimal(5,2) unsigned,
  primary key(documento)
 );

3- Ingrese algunos registros:

 insert into empleados (documento,nombre,sexo,domicilio,fechaingreso,

fechanacimiento,sueldoBasico)
  values ('22333111','Juan Perez','m','Colon 123','1990-02-01',

'1970-05-10',550);
 insert into empleados (documento,nombre,sexo,domicilio,fechaingreso,

fechanacimiento,sueldoBasico)
  values ('25444444','Susana Morales','f','Avellaneda 345','1995-04-01',

'1975-11-06',650);
 insert into empleados (documento,nombre,sexo,domicilio,fechaingreso,

fechanacimiento,sueldoBasico)
  values ('20111222','Hector Pereyra','m','Caseros 987','1995-04-01',

'1965-03-25',510);
 insert into empleados (documento,nombre,sexo,domicilio,fechaingreso,

fechanacimiento,sueldoBasico)
  values ('30000222','Luis Luque','m','Urquiza 456','1980-09-01',

'1980-03-29',700);
 insert into empleados (documento,nombre,sexo,domicilio,fechaingreso,

fechanacimiento,sueldoBasico)
  values ('20555444','Maria Laura Torres','f','San Martin 1122','2000-05-15',

'1965-12-22',700);
 insert into empleados (documento,nombre,sexo,domicilio,fechaingreso,

fechanacimiento,sueldoBasico)
  values ('30000234','Alberto Soto','m','Peru 232','2003-08-15',

'1989-10-10',420);
 insert into empleados (documento,nombre,sexo,domicilio,fechaingreso,

fechanacimiento,sueldoBasico)
  values ('20125478','Ana Gomez','f','Sarmiento 975','2004-06-14',

'1976-09-21',350);
 insert into empleados (documento,nombre,sexo,domicilio,fechaingreso,

fechanacimiento,sueldoBasico)
  values ('24154269','Ofelia Garcia','f','Triunvirato 628','2004-09-23',

'1974-05-12',390);
insert into empleados (documento,nombre,sexo,domicilio,fechaingreso,

fechanacimiento,sueldoBasico)
  values ('30154269','Oscar Mendez','m','Colon 1245','2004-06-23',

'1984-05-14',300);

4- Es política de la empresa festejar cada fin de mes, los cumpleaños de 

todos los empleados que cumplen ese mes. Si los empleados son de sexo femenino, 

se les regala un ramo de rosas, si son de sexo masculino, una corbata. 

La secretaria de la Gerencia necesita saber cuántos ramos de rosas y 
cuántas corbatas debe comprar para el mes de mayo:

5- Se necesita conocer la cantidad de empleados agrupados por año de ingreso 

a la empresa: 

 C) Un comercio guarda la información de sus ventas en una tabla llamada 

"facturas" en la que registra el número de factura, la descripción de los 

items comprados, el precio por unidad de los items y la cantidad.

1- Elimine la tabla si existe.

2- Cree la tabla:

 create table facturas(
  numero int(10) zerofill,
  descripcion varchar(30),
  precioporunidad decimal(5,2) unsigned,
  cantidad tinyint unsigned
 );

3- Ingrese algunos registros:

 insert into facturas values(504,'escuadra 20 cm.',2.5,100);
 insert into facturas values(504,'escuadra 50 cm.',5,80);
 insert into facturas values(2002,'compas plastico',8,120);
 insert into facturas values(2002,'compas metal',15.4,100);
 insert into facturas values(2002,'escuadra 20 cm.',2.5,100);
 insert into facturas values(4567,'escuadra 50 cm.',5,200);

4- Cuente la cantidad de items por factura:
 
5- Sume la cantidad de productos de las facturas:
 
6- Muestre el total en dinero de las facturas: