Count (*) Left Join

[unexes]

Pido disculpas por si este tema no perteneciera a este foro.

Hola me he dado cuenta de un error al contar los registros en mi página y he buscado como solucionarlo pero no veo donde esta el fallo.

Tengo 2 tablas, la primera se llama actividades y la segunda clientes:

La primera (actividades) tiene los siguientes campos:

idActividad-----NomActividad----- Activacion---- Actividad
-------------------------------------------------------------------------------
-----1--------------Neumaticos------------1------------Neumaticos
-----2--------------Peluquerias------------1------------Peluquerias
-----3--------------Carroceria--------------1------------Carroceria
-----4--------------Chapa---------------------1------------Chapa
etc...

La segunda (clientes) tiene los siguientes campos:

Id-------Activación--------Actividades-----------Actividades2-----Url
------------------------------------------------------------------------------------
1------------1----------------Neumaticos------------Carroceria-------http: etc..
2------------1----------------Neumaticos------------Chapa-------------http: etc..
3------------1----------------Carroceria--------------Neumaticos-----http: etc..
4------------1----------------Peluquerias------------------------------------http: etc..

Si funcionara correctamente al contar, el resultado deberia ser:

3 Neumaticos
2 Carroceria
1 Chapa
1 Peluquerias

y lo que me da es un resultado erroneo:

2 Neumaticos
1 Carroceria
1 Chapa
1 Peluquerias

El código que utilizo es este:

Código SQL:

SELECT a.Activacion, a.Actividad, a.Url, a.NomActividad, COUNT(*) AS Total 
FROM actividades a 
LEFT JOIN clientes c1 ON a.Actividad = c1.Actividades 
LEFT JOIN clientes c2 ON a.Actividad = c2.Actividades2 
WHERE a.Activacion = '1' GROUP BY a.Actividad ASC
 

Donde esta el problema, Gracias a tod@s.
P.D.: Cuando pulso me va correctamente a la (Url) y me muestra correctamente todos los que tienen la actividad seleccionada, por ejemplo si pulso sobre Neumaticos me muestra los 3 clientes que tienen puesto Neumaticos aunque me indique erroneamente (2 Neumaticos).

[gnzsoloyo]

Partamos de dos detlles:
1) COUNT() cuenta los registros de una consulta que cumplan con la condición de no ser NULL.
Cuando pones COUNT(*) cuenta los registros devueltos, pero cuanto pones COUNT(campo), cuenta los registros donde "campo" no es NULL. No es lo mismo.

2) LEFT JOIN devuelve los todos registros de la primera tabla tengan o no coincidencia con los de la segunda. Por ello se dice que el orden de los factores en base de datos SI altera el producto.
En este caso, la sintaxis varía de acuerdo a lo que quieras consultar: ¿Las actividades contando clientes , o las actividades DE los clientes?
Su suponemos que no hay actividades sin clientes, en tu consulta el LEFT JOIN devolvería lo mismo que un INNER JOIN.

Si quieres clientes por actividad:

Código SQL:

SELECT a.Activacion, a.Actividad, a.Url, a.NomActividad, COUNT(c1.Actividades) TotalC1, COUNT(c2.Actividades) TotalC2, SUM(COUNT(c1.Actividades) + COUNT(c2.Actividades)) total
FROM actividades a 
LEFT JOIN clientes c1 ON a.Actividad = c1.Actividades 
LEFT JOIN clientes c2 ON a.Actividad = c2.Actividades2 
WHERE a.Activacion = 1 
GROUP BY a.Actividad ASC
 

Mas o menos por acá...

[unexes]

Gracias gnzsoloyo.

He probado pero no consigo hacerlo funcionar me da algunos errores.

Lo que quiero contar es cuantos clientes tengo en cada actividad en el ejemplo que puse al principio de mi tabla en el caso de Neumaticos me debe indicar 3 clientes, los que se corresponde en la tabla clientes a los Id: (1, 2, 3).
El Id (1) tiene en la tabla clientes en el campo Actividades la actividad (Neumaticos)
El Id (2) tiene en la tabla clientes en el campo Actividades la actividad (Neumaticos)
El Id (3) tiene en la tabla clientes en el campo Actividades2 la actividad (Neumaticos)

Los errores que me da el código que tan amablemente has puesto son:

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\wamp\www\Tu Zona Valencia-2014-02-27\indexpruebacontarregistros.php on line 132

esta linea dice:

while ($registroactividad = mysql_fetch_array($tablaactividad)) {

y el otro error es:

Warning: mysql_free_result() expects parameter 1 to be resource, boolean given in C:\wamp\www\Tu Zona Valencia-2014-02-27\indexpruebacontarregistros.php on line 140

Esta linea dice:

mysql_free_result($tablaactividad);

Espero que me puedas ayudar porque estoy perdido, gracias

[gnzsoloyo]

Ninguno de esos errores son de MySQL. Son de PHP, y programación es OFF TOPIC en este foro.
En todo caso deberías verificar manualmente en phpMyadmin si la consulta trae resultados.
SI trae resultados al correrlo a mano, pero no en PHP, el error está en las variables de PHP.
Si los resultados no son los epserados, entonces las condiciones de la consulta no son correctas.
SI generea un error de sintaxis, necesitamos saber cuál es el error y por ende necesitamos saber qué mensaje de eror genera MySQL (lo que diga PHP no nos interesa).

En cualquiera de los casos necesitaríamos ver cómo seria la tabla resultado, cuál es la que obtienes, y cuales son los datos reales que tienes para hacer el calculo.

No postees código PHP. Nos veremos obligados a borrarlo (reglas del foro de BBDD).

[unexes]

gnzsoloyo
Los datos reales que tengo son los que estan puestos en el ejemplo.
la consulta manual en phpMyadmin no muestra resultados.
El error de sintaxis que genera es:
[QUOTE#1111 - Invalid use of group function
[/QUOTE]
La consulta que tenia al principio si me da resultados aunque erroneos.
He probado alguna cosa pero sigo sin hacerla funcionar.
Gracias por tu paciencia

[gnzsoloyo]

1111 - Invalid use of group function

Eso implica que se está usando mal una de las funciones. Probablemente es porque hay que resolver mejor la suma...
Probemos:

Código SQL:

SELECT 
    a.Activacion, 
    a.Actividad, 
    a.Url, 
    a.NomActividad, 
    COUNT(c1.Actividades) TotalC1, 
    COUNT(c2.Actividades) TotalC2, 
    SUM(IF(c1.Actividades IS NULL, 0, 1) + IF(c2.Actividades IS NULL, 0, 1)) total
FROM actividades a 
    LEFT JOIN clientes c1 ON a.Actividad = c1.Actividades 
    LEFT JOIN clientes c2 ON a.Actividad = c2.Actividades2 
WHERE a.Activacion = 1 
GROUP BY a.Actividad ASC
 

[unexes]

Bueno de momento no da errores,
Pero sigue contando mal
Ahora en Neumaticos me dice que tengo 4 clientes con actividad Neumaticos y me deberia decir 3.
He hecho una comprobacion y me da un resultado estraño que no entiendo si tengo 2 veces el campo Neumaticos en Actividades y 1 vez el campo Neumaticos en Actividades2 me dobla la cantidad del numero de veces que se encuentra en el campo Actividades, lo correcto seria 3 y me da 4, osea que si en el campo actividades se encuentra 2 veces la actividad Neumaticos y en el campo Actividades2 se encuentra tambien 2 veces el resultado que sale es 8 en lugar de lo correcto que serian 4 lo que hace es 2x2x2, cada vez que encuentra el campo en actividades2 dobla el numero de las veces que se encuentra en Actividades, no lo entiendo,

[gnzsoloyo]

Es un efecto del uso de LEFT JOIN en un esquema de estrella. Genera un error lógico de difícil detección.

Cuando lelgue a casa te lo explico un poco mejor, pero de entrada, una parte del problema puede ser un diseño defectuoso de datos...

[leonardo_josue]

Hola unexes:

Tal como lo adelantó gnzsoloyo, tienes un problema grave de diseño que unido con el uso del LEFT JOIN está ocasionando más dolores de cabeza que los "normales" para cualquier DBA... El que tengas dobles referencias hacia una misma tabla (con las Actividades 1 y 2), es un error de diseño y de normalizacion, sobre todo porque veo que uno de los campos puede quedar como null...

Una de las primeras reglas de la normalización de BD's es que NO DEBES TENER LLAVES FORÁNEAS EN CAMPOS QUE ACEPTEN NULOS... en lugar de tener estos dos campos, debes hacer una tabla referencia y colocar un atributo que te indique si es la actividad 1 o la actividad 2, es decir, algo como esto:

Código:

Id-------Activación--------Actividades----------------Url---- tipoActividad
1------------1----------------Neumaticos------------http: etc..---1
1------------1----------------Carroceria-------http: etc..---2
2------------1----------------Neumaticos------------http: etc..---|
2------------1----------------Chapa-------------http: etc..---2
3------------1----------------Carroceria--------------http: etc..---1
3------------1----------------Neumaticos-----http: etc..---2
4------------1----------------Peluquerias-----http: etc..---1

¿Se entiende la idea?

Esto es solo una idea, en realidad son muchos aspectos adicionales que debes tomar en cuenta. Si tienes oportunidad de modificar tu modelo de datos, hazlo... te evitarás más problemas en el futuro... (El que tengas pereza no cuenta como una razón de peso para no modificar tu diseño de tablas).

ahora bien, volviendo a tu problema original, tal como te diste cuenta, el conteo de registros cuando utilizas LEFT JOIN puede no ser tan simple... por lo que veo lo único que te interesa es saber cuántas actividades de cada categoría hay en tu tabla clientes, podrías hacer algo como esto:

Código MySQL:

mysql> SELECT actividad, COUNT(actividad) FROM
    -> ( SELECT actividades actividad FROM clientes
    ->   UNION ALL
    ->   SELECT actividades2 actividad FROM clientes ) T
    -> WHERE actividad IS NOT NULL
    -> GROUP BY actividad
    -> ORDER BY 2 DESC;
+-------------+------------------+
| actividad   | COUNT(actividad) |
+-------------+------------------+
| Neumaticos  |                3 |
| Carroceria  |                2 |
| Peluquerias |                1 |
| Chapa       |                1 |
+-------------+------------------+
4 rows in set (0.00 sec)
 

es decir, con el UNION ALL "corriges" el error de diseño que tienes en tus tablas, al convertir una doble referencia en una referencia simple...

Dale un vistazo para ver si te sirve el código.

Saludos
Leo

[unexes]

Gracias gnzsoloyo, espero tu ayuda y si tengo que cambiar el diseño de mis tablas no me importa, lo que quiero es que me funcione, porque llevo bastante tiempo haciendo pruebas y buscando la solución en la red y no he podido lograrlo.
Ya habia mandado este mensaje cuando he visto el tuyo leonardo_josue así que lo he vuelto a editar para contestar tu amable ayuda.
Veras no entiendo muy bien lo que me dices osea la idea que me planteas, perdoname ya que soy novatillo se nota verdad y todavia me pierdo.
Bueno intentare esplicarte brevemente porque lo plantee así, la tabla "clientes" logicamente tiene mas campos como por ejemplo el nombre del cliente que no lo he puesto al plantear el problema porque he considerado que no hacia falta y lo que no quiero es repetir cada cliente si tiene 2 actividades.
Veras la cuestión y no me importaria cambiar mi base de datos es que un mismo cliente cuando se de de alta puede tener 1 ó 2 actividades distintas por las que quiere aparecer, es por lo que hay 2 campos Actividades, el (Actividades y el Actividades2).
El cliente introduce a traves de un formulario su actividad o sus 2 actividades, por eso a veces se da el caso que un cliente tenga la primera actividad con un dato y la segunda Actividad2 sin nada, o que la actividad segunda de un cliente coincida con la primera de otro cliente, es por eso que necesito contar cuantos clientes hay de cada actividad, independientemente si ha puesto la actividad en Actividades o en Actividades2.
La verdad pense que era mas facil, la solución. Por mi parte estoy dispuesto a cambiar lo que haga falta y os doy de nuevo las gracias por toda vuestra ayuda.
Espero me comenteis algo.

[gnzsoloyo]

Como ya te ha aclarado Leonardo_Josue, el diseño de tablas no es correcto para lo que quieres. Es mejor plantearlo al menos con tres tablas, y yo lo haría mas o menos así:

Código MySQL:

mysql> DROP TABLE IF EXISTS clientes_actividades;
Query OK, 0 rows affected (0.00 sec)
 
mysql> DROP TABLE IF EXISTS actividades;
Query OK, 0 rows affected (0.02 sec)
 
mysql> DROP TABLE IF EXISTS clientes;
Query OK, 0 rows affected (0.02 sec)
 
mysql>
mysql> CREATE TABLE IF NOT EXISTS actividades (
    ->     idActividad INT UNSIGNED PRIMARY KEY,
    ->     NomActividad VARCHAR(50)
    -> );
Query OK, 0 rows affected (0.02 sec)
 
mysql>
mysql> CREATE TABLE IF NOT EXISTS clientes (
    ->     IdCliente INT UNSIGNED PRIMARY KEY,
    ->     Url VARCHAR(300)
    -> );
Query OK, 0 rows affected (0.03 sec)
 
mysql>
mysql> CREATE TABLE IF NOT EXISTS clientes_actividades (
    ->     idActividad INT UNSIGNED,
    ->     IdCliente INT UNSIGNED,
    ->     Activado INT UNSIGNED,
    ->     PRIMARY KEY (idActividad , IdCliente),
    ->     FOREIGN KEY (idcliente)
    ->         REFERENCES clientes (idcliente),
    ->     FOREIGN KEY (idactividad)
    ->         REFERENCES actividades (idactividad)
    -> );
Query OK, 0 rows affected (0.00 sec)
 
mysql>
mysql> INSERT INTO actividades
    -> VALUES(1, 'Neumaticos'),
    -> (2, 'Peluquerias'),
    -> (3, 'Carroceria'),
    -> (4, 'Chapa');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
 
mysql>
mysql>
mysql>
mysql> INSERT INTO clientes
    -> VALUES
    -> (1, 'http://aa.aa.com'),
    -> (2, 'http://bb.bb.com'),
    -> (3, 'http://cc.cc.com'),
    -> (4, 'http://dd.dd.com');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
 
mysql>
mysql> INSERT INTO clientes_actividades
    -> VALUES
    -> (1, 1, 1), (3, 1, 1),
    -> (1, 2, 1), (4, 2, 1),
    -> (3, 3, 1), (1, 3, 1),
    -> (2, 4, 1);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0
 

En ese contexto, la query sería:

Código SQL:

mysql>
mysql> SELECT
    ->     c.idcliente, c.url, COUNT(*) actividades
    -> FROM
    ->     clientes c
    ->         INNER JOIN
    ->     clientes_actividades ca ON c.idcliente = ca.idcliente
    ->         INNER JOIN
    ->     actividades a ON ca.idactividad = a.idactividad
    -> GROUP BY c.url;
+-----------+------------------+-------------+
| idcliente | url              | actividades |
+-----------+------------------+-------------+
|         1 | http://aa.aa.com |           2 |
|         2 | http://bb.bb.com |           2 |
|         3 | http://cc.cc.com |           2 |
|         4 | http://dd.dd.com |           1 |
+-----------+------------------+-------------+
4 ROWS IN SET (0.00 sec)
 

[unexes]

Gracias gnzsoloyo
perdonar la tardanza en contestar pero es que aqui ya estamos metidos de lleno en las Fallas
y eso me genera mucho trabajo y poco tiempo libre.

Veras voy a probar en los proximos días lo que me aconsejais que tengo que hacer, en cuanto lo haya probado os comentare y os dire como funciona, Gracias a los dos.

Un saludo.