40 - Combinaciones y funciones de agrupamiento

Podemos usar "group by" y las funciones de agrupamiento con combinaciones de tablas.

Problema resuelto

Una librería almacena la información de sus libros para la venta en dos tablas, "libros" y "editoriales".
Borramos las tablas si existen:

drop table if exists libros;
drop table if exists editoriales;

Creamos las tablas con las siguientes estructuras:

create table libros(
	codigo integer primary key,
	titulo text,
	autor text, 
	precio real,
	codigoeditorial integer	
);

create table editoriales(
	codigo integer primary key,
	nombre text
);

Almacenamos los siguientes datos de prueba:

 insert into editoriales(nombre) values('Planeta');
 insert into editoriales(nombre) values('Emece');
 insert into editoriales(nombre) values('Siglo XXI');

 insert into libros (titulo, autor, codigoeditorial, precio)
  values('El aleph', 'Borges', 2, 34);
 insert into libros (titulo, autor, codigoeditorial, precio)
  values('Antología poética', 'Borges', 1, 39.50);
 insert into libros (titulo, autor, codigoeditorial, precio)
  values('Java en 10 minutos', 'Mario Molina', 1, 50.50);
 insert into libros (titulo, autor, codigoeditorial, precio)
  values('Alicia en el pais de las maravillas', 'Lewis Carroll', 2, 19.90);
 insert into libros (titulo, autor, codigoeditorial, precio)
  values('Martin Fierro', 'Jose Hernandez', 2, 25.90);
 insert into libros (titulo, autor, codigoeditorial, precio)
  values('Martin Fierro', 'Jose Hernandez', 3, 16.80);

Contamos la cantidad de libros de cada editorial consultando ambas tablas:

 select nombre as editorial,
  count(*) as cantidad
  from editoriales as e
  join libros as l
  on codigoeditorial=e.codigo
  group by e.nombre;

Buscamos el libro más costoso de cada editorial con un "left join":

 select nombre as editorial,
  max(precio) as mayorprecio
  from editoriales as e
  left join libros as l
  on codigoeditorial=e.codigo
  group by nombre;

Problema propuesto

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 y en otra tabla llamada "ciudades" los nombres de las ciudades.

  1. Eliminar las tablas "visitantes" y "ciudades" si existen.

  2. Crear las tablas "visitantes" y "ciudades" con las siguientes estructuras:

     create table ciudades(
  codigo integer primary key,
  nombre text
 );
 
 create table visitantes(
  codigo integer primary key,
  nombre text,
  edad integer,
  sexo text,
  domicilio text,
  codigociudad integer,
  mail text,
  montocompra real
 );

  3. Cargar los siguientes datos para su prueba:

     insert into ciudades(nombre) values('Cordoba');
 insert into ciudades(nombre) values('Carlos Paz');
 insert into ciudades(nombre) values('La Falda');
 insert into ciudades(nombre) values('Cruz del Eje');
 
 insert into visitantes(nombre, edad, sexo, domicilio, codigociudad, mail, montocompra) 
  values ('Susana Molina', 35,'f','Colon 123', 1, null,59.80);
 insert into visitantes(nombre, edad, sexo, domicilio, codigociudad, mail, montocompra) 
  values ('Marcos Torres', 29,'m','Sucre 56', 1, 'marcostorres@hotmail.com',150.50);
  insert into visitantes(nombre, edad, sexo, domicilio, codigociudad, mail, montocompra) 
   values ('Mariana Juarez', 45,'f','San Martin 111',2,null,23.90);
  insert into visitantes(nombre, edad, sexo, domicilio, codigociudad, mail, montocompra) 
   values ('Fabian Perez',36,'m','Avellaneda 213',3,'fabianperez@xaxamail.com',0);
  insert into visitantes(nombre, edad, sexo, domicilio, codigociudad, mail, montocompra) 
   values('Alejandra Garcia',28,'f',null,2,null,280.50);
  insert into visitantes(nombre, edad, sexo, domicilio, codigociudad, mail, montocompra) 
   values ('Gaston Perez',29,'m',null,5,'gastonperez1@gmail.com',95.40);
  insert into visitantes(nombre, edad, sexo, domicilio, codigociudad, mail, montocompra) 
   values ('Mariana Juarez',33,'f',null,2,null,90);

  4. Cuente la cantidad de visitas por ciudad mostrando el nombre de la ciudad.

  5. Muestre el promedio de gastos de las visitas agrupados por ciudad y sexo.

  6. Muestre la cantidad de visitantes con mail, agrupados por ciudad.

  7. Obtenga el monto de compra más alto de cada ciudad.

Solución 
drop table if exists ciudades;
drop table if exists visitantes;

 create table ciudades(
  codigo integer primary key,
  nombre text
 );
 
 create table visitantes(
  codigo integer primary key,
  nombre text,
  edad integer,
  sexo text,
  domicilio text,
  codigociudad integer,
  mail text,
  montocompra real
 ); 

 insert into ciudades(nombre) values('Cordoba');
 insert into ciudades(nombre) values('Carlos Paz');
 insert into ciudades(nombre) values('La Falda');
 insert into ciudades(nombre) values('Cruz del Eje');
 
 insert into visitantes(nombre, edad, sexo, domicilio, codigociudad, mail, montocompra) 
  values ('Susana Molina', 35,'f','Colon 123', 1, null,59.80);
 insert into visitantes(nombre, edad, sexo, domicilio, codigociudad, mail, montocompra) 
  values ('Marcos Torres', 29,'m','Sucre 56', 1, 'marcostorres@hotmail.com',150.50);
  insert into visitantes(nombre, edad, sexo, domicilio, codigociudad, mail, montocompra) 
   values ('Mariana Juarez', 45,'f','San Martin 111',2,null,23.90);
  insert into visitantes(nombre, edad, sexo, domicilio, codigociudad, mail, montocompra) 
   values ('Fabian Perez',36,'m','Avellaneda 213',3,'fabianperez@xaxamail.com',0);
  insert into visitantes(nombre, edad, sexo, domicilio, codigociudad, mail, montocompra) 
   values('Alejandra Garcia',28,'f',null,2,null,280.50);
  insert into visitantes(nombre, edad, sexo, domicilio, codigociudad, mail, montocompra) 
   values ('Gaston Perez',29,'m',null,5,'gastonperez1@gmail.com',95.40);
  insert into visitantes(nombre, edad, sexo, domicilio, codigociudad, mail, montocompra) 
   values ('Mariana Juarez',33,'f',null,2,null,90); 
 
 select c.nombre,
  count(*) as cantidad
  from ciudades as c
  join visitantes as v
  on codigociudad=c.codigo
  group by c.nombre;
  
 select c.nombre,sexo,
  avg(montocompra) as promediodecompra
  from ciudades as c
  join visitantes as v
  on codigociudad=c.codigo
  group by c.nombre,sexo;  
  
 select c.nombre,
  count(mail) as tienenmail
  from ciudades as c
  join visitantes as v
  on codigociudad=c.codigo
  group by c.nombre;  
  

 select c.nombre,
  max(montocompra)
  from visitantes as v
  join ciudades as c
  on codigociudad=c.codigo
  group by c.nombre;

Ejecución de ejercicios online

Puede ejecutar comandos de SQLite directamente en el sitio sin tener que instalar nada en su computadora.

Resultado.....
Retornar