Search code examples
sqlms-accessms-access-2007

SQL for COUNT and GROUP BY

This is my first experience posting a question in this site :) so, i´m in trouble. I have a database in access, that i connect with ODBC.

Database have 3 tables:

nombres : (-idnombre, -nombre)
rutas   : (-idruta, ruta)
fechas  : (-idfecha, idruta, idnombre, fecha)

Sample data : 

nombres:(1,nombreA),(2,nombreB)
Rutas:(1,rutaA),(2,RutaB)
fechas:(1,1,1,28/06/2013), (2,2,1,28/06/2013), (3,2,2,28/06/2013),(4,2,2,28/06/2013)

so i need this output ( third field is a count ):

rutaA - nombreA - 1 time
rutaA - nombreB - 0 times
rutaB - nombreA - 1 time
rutaB - nombreB - 2 times

my sql is:

SELECT rutas.ruta, nombres.nombre, Count(fechas.idruta) AS CuentaDeidruta
FROM rutas INNER JOIN 
    (nombres INNER JOIN fechas ON nombres.idnombre = fechas.idnombre) 
    ON rutas.idruta = fechas.idruta
GROUP BY rutas.ruta, nombres.nombre;

its ok, but is not showing zero counts. so my output is:

rutaA - nombreA - 1 time
rutaB - nombreA - 1 time
rutaB - nombreB - 2 times

I tried with left join, so i get some errors.

Solution

  • I think you're looking for the Cartesian Product of Rutas and Nombres, thus giving you something like this as in your sample data, there is no correlation between rutaA and nombreB.

    SELECT t.ruta, t.nombre, Count(fechas.idruta) AS CuentaDeidruta 
FROM (
    SELECT rutas.idruta, rutas.ruta, nombres.idnombre, nombres.nombre
    FROM rutas, nombres
  ) t
  LEFT JOIN fechas ON 
    t.idnombre = fechas.idnombre AND fechas.idruta = t.idruta
GROUP BY t.ruta, t.nombre;

    This is for SQL Server, but should port over to MS Access pretty closely. Also please note the usage of a LEFT JOIN to ensure you get the 0s you were missing.