Problema con consulta Counts!

[sergioo]

Buenas, voy a intentar explicarlo de la mejor manera porque soy bastante amateur en estas cosillas.

Llevo todo el día dandole vueltas y probando distintas cosas que he visto por internet y nada me vale

Tengo 3 tablas: Lectura, Estacion y TipoEstacion

Código SQL:

SELECT ES.id, ES.nombre AS 'nombreEstacion', TE.nombre AS 'Tipo estación', COUNT(LE.id AS 'Cuenta'
FROM estacion AS ES LEFT JOIN
         lectura AS LE ON LE.idEstacion=ES.id LEFT JOIN
         deutztraza.tipo_estacion AS TE ON TE.id=ES.idTipoEstacion
WHERE ES.eliminada=0 AND LE.resultadoNOK=2 //esto es indiferente
GROUP BY ES.id
 

el resultado es:
ID nombreEstacion Tipo count
85 A Pistola 1
94 B Pistola 2
100 D Pistola 1
106 F Pistola 4
109 G Pistola 3
118 I Pistola 1
121 K Pistola 1

Mi problema es que hay un monton de IDs con su nombreEstacion que tienen counts 0 y no salen y me gustaria que saliesen
Alguien sería tan amable de ayudarme? llevo todo el dia pegandome con ello...
Un saludo!!!

[gnzsoloyo]

Probemos:

Código SQL:

SELECT 
    ES.id, 
    ES.nombre nombreEstacion, 
    TE.nombre Tipo estación, 
    COUNT(LE.id) Cuenta
FROM estacion ES 
    LEFT JOIN lectura LE ON ES.id = LE.idEstacion
    LEFT JOIN deutztraza.tipo_estacion ES.idTipoEstacion = TE ON TE.id
WHERE 
    ES.eliminada=0 
    AND LE.resultadoNOK=2 
    OR LE.idEstacion IS NULL
GROUP BY ES.id
 

¿Con qué DBMS estás trabajando?

[sergioo]

mmm con mysql
creo que funciona pero no entiendo.
Veo mas o menos el significado de left o right join pero con eso me he perdido ya...
Me puedes explicar un poco para tontos como va el funcionamiento para que funcione?

Estoy intentando sacar la consulta sin el campo count pero con todas las estaciones y un ultimo campo (no se cual) a null pero no se ni como hacerlo... Estoy muy frustrado porque por el momento creo que entiendo las cosas pero al siguiente me hundo

[gnzsoloyo]

LEFT JOIN devuelve todo lo que está en la tabla izquierda (la primera) tenga o no coincidencias con la de la derecha. Eso es básicamente lo que hace.
Ahora bien, en ocasiones MySQL, cuando defines ciertas condiciones en el WHERE puede implementar el LEFT JOIN como si le indicaras INNER JOIN, y eso es lo que estaba pasando. El filtro sobre los otros campos excluye los casos nulos de la tabla derecha. Por eso, cuando haces un WHERE con condiciones concretas, debes sí o si indicar la condición nula. Simplemente tomas cualquier campo de la segunda tabla y le indicas "OR campo IS NULL".

Nota bene: No es buena idea hacer LEFT JOIN entre más de dos tablas si el LEFT JOIN aplica sobre la primera en todos los casos, porque pueden generarse productos cartesianos y datos inconsistentes. Se producen relaciones en estrella indeseados.
Hay que analizar bien el caso.

Nota Bene 2: La relación indicada en el ON debe cumplir con la posición de las tablas en el LEFT JOIN. No las inviertas o los resultados pueden ser incorrectos.

[sergioo]

mmmm una ultima duda por hoy (muchas gracias por cierto)
Si yo tengo esta consulta que acabamos de poner donde saco
D nombreEstacion Tipo countTIPO1 CountTIPO2 COUNTTIPO3
85 A Pistola 1
94 B Pistola 2
100 D Pistola 1
106 F Pistola 4
109 G Pistola 3
118 I Pistola 1
121 K Pistola 1

podría hacerlo de alguna manera rápida si el countTIPO1 --->count(LE.id) sale basicamente en el where donde pone "LE.resultadoNOK=2"

El countTIPO2 seria exactamente la misma consulta pero "LE.resultadoNOK=1"

y El countTIPO3 seria exactamente la misma consulta pero "LE.resultadoNOK=0"

Habia pensado en hacer en:
SELECT
ES.id,
ES.nombre AS nombreEstacion,
TE.nombre AS Tipo estación,
COUNT(LE.id) AS Cuenta,
(METER LA CONSULTA2),
(METER LA CONSULTA3),
FROM .....

Habria alguna forma mas ingeniosa o mejor de hacerlo (de manera sencilla claro esta), o asi ya esta bien


POR ULTIMO y siento abusar. Tengo a veces problemas para entender el GROUP BY. Es decir cuando es un caso sencillo de un campo y un count o sum lo entiendo y lo veo claro.
Pero si tengo un SELECT id,Nombre,APELLIDO1,APELLIDO2....
GROUP BY (¿?)

CUando no hay campos tipo counts y tal, no entiendo o no veo muy bien cuando poner en group by:
A) No poner group by
B) Group by id,
C) group by id,nombre
D)group by id,nombre,Apellido1

gracias por todo de verdad!

[gnzsoloyo]

Con un "truco":

Código MySQL:

SELECT 
    ES.id, 
    ES.nombre nombreEstacion, 
    TE.nombre Tipo estación, 
    COUNT(LE.id) Cuenta,
    SUM(IF(LE.resultadoNOK=1, 1, 0)) NoOk,
    SUM(IF(LE.resultadoNOK=0, 1, 0)) Ok
FROM estacion ES 
    LEFT JOIN lectura LE ON ES.id = LE.idEstacion
    LEFT JOIN deutztraza.tipo_estacion ES.idTipoEstacion = TE ON TE.id
WHERE 
    ES.eliminada=0 
    OR LE.idEstacion IS NULL
GROUP BY ES.id
 

Que también puede ser así:

Código MySQL:

SELECT 
    ES.id, 
    ES.nombre nombreEstacion, 
    TE.nombre Tipo estación, 
    COUNT(LE.id) Cuenta,
    SUM(LE.resultadoNOK=1) NoOk,
    SUM(LE.resultadoNOK=0) Ok
FROM estacion ES 
    LEFT JOIN lectura LE ON ES.id = LE.idEstacion
    LEFT JOIN deutztraza.tipo_estacion ES.idTipoEstacion = TE ON TE.id
WHERE 
    ES.eliminada=0 
    OR LE.idEstacion IS NULL
GROUP BY ES.id
 

[sergioo]

ufff me he perdido. No se si me he explicado bien, en vez de tener un solo count, tengo 3 de la misma variable y la consulta solo cambiaria en que para:
primer count en el where tiene de diferente lo de LE.resultadoNOK=0
para el 2º count LE.resultadoNOK=1
para el 3º count LE.resultadoNOK=2

he encontrado una solucion asi :
SUM(CASE WHEN LE.resultadoNOK = 0 THEN 1 ELSE 0 END) AS COUNT1,
SUM(CASE WHEN LE.resultadoNOK = 1 THEN 1 ELSE 0 END) AS COUNT2,
SUM(CASE WHEN LE.resultadoNOK = 2 THEN 1 ELSE 0 END) AS COUNT3

- no entiendo muy bien esa sintaxis del sum ahi por lo que no se como arreglarlo o como podria usarlo en un futuro

- De todas formas supongo que no influira que luego quiera hacer un UNION con una select exactamente igual estructura que todo esto pero con una tabla lecturaFF(copia con mas registros)
- Una cosa que se me paso por alto, me dijiste que al hacer LEFT o RIGHT join tenia que poner un campo cualquiera en el where a Is null. Pero al tener 2 LEF>Ts join, tendria que hacer con dos campos? 1 de cada tabla

[sergioo]

Iniciado por gnzsoloyo

Con un "truco":

Código MySQL:

SELECT 
    ES.id, 
    ES.nombre nombreEstacion, 
    TE.nombre Tipo estación, 
    COUNT(LE.id) Cuenta,
    SUM(IF(LE.resultadoNOK=1, 1, 0)) NoOk,
    SUM(IF(LE.resultadoNOK=0, 1, 0)) Ok
FROM estacion ES 
    LEFT JOIN lectura LE ON ES.id = LE.idEstacion
    LEFT JOIN deutztraza.tipo_estacion ES.idTipoEstacion = TE ON TE.id
WHERE 
    ES.eliminada=0 
    OR LE.idEstacion IS NULL
GROUP BY ES.id
 

Que también puede ser así:

Código MySQL:

SELECT 
    ES.id, 
    ES.nombre nombreEstacion, 
    TE.nombre Tipo estación, 
    COUNT(LE.id) Cuenta,
    SUM(LE.resultadoNOK=1) NoOk,
    SUM(LE.resultadoNOK=0) Ok
FROM estacion ES 
    LEFT JOIN lectura LE ON ES.id = LE.idEstacion
    LEFT JOIN deutztraza.tipo_estacion ES.idTipoEstacion = TE ON TE.id
WHERE 
    ES.eliminada=0 
    OR LE.idEstacion IS NULL
GROUP BY ES.id
 

cuando puedas si es posible me podrías resolver las ultimas dudas, gracias!

[sergioo]

Iniciado por sergioo

ufff me he perdido. No se si me he explicado bien, en vez de tener un solo count, tengo 3 de la misma variable y la consulta solo cambiaria en que para:
primer count en el where tiene de diferente lo de LE.resultadoNOK=0
para el 2º count LE.resultadoNOK=1
para el 3º count LE.resultadoNOK=2

- no entiendo muy bien esa sintaxis del sum ahi por lo que no se como arreglarlo o como podria usarlo en un futuro

he encontrado una solucion asi :
SUM(CASE WHEN LE.resultadoNOK = 0 THEN 1 ELSE 0 END) AS COUNT1,
SUM(CASE WHEN LE.resultadoNOK = 1 THEN 1 ELSE 0 END) AS COUNT2,
SUM(CASE WHEN LE.resultadoNOK = 2 THEN 1 ELSE 0 END) AS COUNT3

pero no me va como deberia...

- De todas formas supongo que no influira que luego quiera hacer un UNION con una select exactamente igual estructura que todo esto pero con una tabla lecturaFF(copia con mas registros)
- Una cosa que se me paso por alto, me dijiste que al hacer LEFT o RIGHT join tenia que poner un campo cualquiera en el where a Is null. Pero al tener 2 LEF>Ts join, tendria que hacer con dos campos? 1 de cada tabla
COMO es posible que si hago los counts en diferentes consultas me devuelva un numero de rows distintos???????

[sergioo]

Buenos días, tengo una pregunta para ustedes,
Necesito que mi consulta me de el nº de counts group by contenedor


SELECT C.Contenedor, count(DC.Contenedor) as NUM
FROM contenedores AS C, det_contenedores as DC
WHERE C.Contenedor=DC.Contenedor
GROUP BY C.Contenedor

Pero solo me aparecen los resultados de C.Contenedor que su count sea >0

El resultado sería

A->3
B->7
D->6

Pero a mi me gustaría:

A->3
B->7
C->0
D->6
E->0

Alguna idea de como hacer que aparezcan los 0? gracias!

[sergioo]

Muy buenas, ayer inicie una pregunta que derivo en otras que espero que alguno me pueda ayudar. Empiezo....
--------PROBLEMA 1--------------------------
Tengo 3 tablas: Lectura, Estacion y TipoEstacion

Código:

SELECT ES.id, ES.nombre AS 'nombreEstacion', TE.nombre AS 'Tipo estación', COUNT(LE.id AS 'Cuenta'
FROM estacion AS ES LEFT JOIN
         lectura AS LE ON LE.idEstacion=ES.id LEFT JOIN
         tipo_estacion AS TE ON TE.id=ES.idTipoEstacion
WHERE ES.eliminada=0 AND LE.resultadoNOK=2
GROUP BY ES.id

el resultado es:
ID nombreEstacion Tipo count
85 A Pistola 1
94 B Pistola 2
100 D Pistola 1
106 F Pistola 4
109 G Pistola 3
118 I Pistola 1
121 K Pistola 1

Mi problema es que hay un monton de IDs con su nombreEstacion que tienen counts 0 y no salen.

Solucion:

Código:

SELECT 
    ES.id, 
    ES.nombre nombreEstacion, 
    TE.nombre Tipo estación, 
    COUNT(LE.id) Cuenta
FROM estacion ES 
    LEFT JOIN lectura LE ON ES.id = LE.idEstacion
    LEFT JOIN tipo_estacion ES.idTipoEstacion = TE ON TE.id
WHERE 
    ES.eliminada=0 
    AND LE.resultadoNOK=0 
    OR LE.idEstacion IS NULL
GROUP BY ES.id

No entiendo muy bien porque hay que añadir un "OR LE.idEstacion IS NULL" para que ocurra esto La solución que se me dio es que al hacer lefts join cuando haces un WHERE con condiciones concretas, debes sí o si indicar la condición nula. Simplemente tomas cualquier campo de la segunda tabla y le indicas "OR campo IS NULL.

En mi caso al hacer 2 lefts join deberia incluir otra condicion is null?

-----------------PROBLEMA 2----------------
Siguiente DUDA
si quiero hacer esto mismo pero con 3 counts distintos donde lo unico que cambiaria seria la condicion WHERE
LE.resultadoNOK=0 --->count1
LE.resultadoNOK=1 ---->count2
LE.resultadoNOK=2 ---->count3

La solucioin que se me dio para esto no me funciona bien:
COUNT(LE.id) Cuenta,
SUM(IF(LE.resultadoNOK=1, 1, 0)) NoOk,
SUM(IF(LE.resultadoNOK=0, 1, 0)) Ok

Creo que algo en el orden de ahi esta mal. Y como no entiendo el significado de como va esos SUM(IF)) no se arreglarlo.
-----------------------------

---------------PROBLEMA3-------------
Como es posible que si yo hago consultas independientes para cada uno de los counts me salganun numero de contenedores (rows) distintos. Es que algo estoy haciendo mal verdad???