sql - una - Contando el número de filas unidas en la combinación izquierda

sum count sql

Question

Estoy intentando escribir una consulta agregada en SQL que devuelve el recuento de todos los registros unidos a un registro determinado en una tabla; Si no se unieron registros al registro dado, entonces el resultado para ese registro debería ser 0 :

Datos

Mi base de datos tiene este aspecto (desafortunadamente, no puedo cambiar la estructura):

MESSAGE
----------------------------------------------
MESSAGEID   SENDER        SUBJECT
----------------------------------------------
1           Tim           Rabbit of Caerbannog
2           Bridgekeeper  Bridge of Death

MESSAGEPART
----------------------------------------------
MESSAGEID   PARTNO        CONTENT
----------------------------------------------
1           0             (BLOB)
1           1             (BLOB)
3           0             (BLOB)

( MESSAGEPART tiene una PRIMARY KEY("MESSAGEID", "PARTNO") compuesta PRIMARY KEY("MESSAGEID", "PARTNO") )

Salida deseada

Teniendo en cuenta los datos anteriores, debería obtener algo como esto:

MESSAGEID   COUNT(*)
-----------------------------------------------
1           2
2           0

Parece obvio que necesito hacer una combinación a la izquierda en la tabla MESSAGE , pero ¿cómo devuelvo un conteo de 0 para las filas donde las columnas unidas de MESSAGEPART son NULL ? He intentado lo siguiente:

Lógica

He intentado

SELECT m.MESSAGEID, COUNT(*) FROM MESSAGE m
LEFT JOIN MESSAGEPART mp ON mp.MESSAGEID = m.MESSAGEID
GROUP BY m.MESSAGEID;

Sin embargo, esto devuelve

MESSAGEID   COUNT(*)
-----------------------------------------------
1           2
2           1

También he intentado

SELECT mp.MESSAGEID, COUNT(*) FROM MESSAGE m
LEFT JOIN MESSAGEPART mp ON mp.MESSAGEID = m.MESSAGEID
GROUP BY mp.MESSAGEID;

pero esto vuelve

MESSAGEID   COUNT(*)
-----------------------------------------------
1           2
            1

¿Qué estoy haciendo mal aquí?

Answer 1

Qué tal algo como esto:

SELECT m.MESSAGEID, sum((case when mp.messageid is not null then 1 else 0 end)) FROM MESSAGE m
LEFT JOIN MESSAGEPART mp ON mp.MESSAGEID = m.MESSAGEID
GROUP BY m.MESSAGEID;

La función COUNT () contará cada fila, incluso si tiene un valor nulo. Usando SUM () y CASE, puede contar solo valores no nulos.

EDITAR: Una versión más simple tomada del comentario superior:

SELECT m.MESSAGEID, COUNT(mp.MESSAGEID) FROM MESSAGE m
LEFT JOIN MESSAGEPART mp ON mp.MESSAGEID = m.MESSAGEID
GROUP BY m.MESSAGEID;

Espero que ayude.

Answer 2

Primero debes contar en tu tabla de mensajes de texto antes de unirte, creo. Prueba esto:

   SELECT m.MessageId
        , COALESCE(c, 0) as myCount
     FROM MESSAGE m
LEFT JOIN (SELECT MESSAGEID
                , count(*) c 
             FROM MESSAGEPART 
            GROUP BY MESSAGEID) mp
       ON mp.MESSAGEID = m.MESSAGEID