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