Complex LEFT JOIN not working as expected

Question

DBMS is intersystems-cache!

Here is my full query:

SELECT  m.Name AS MessageType, COUNT(l.name) AS MessageCount, CAST(AVG(ResponseTime) AS DECIMAL(5, 2)) AS AvgResponseTime
FROM 
        (SELECT DISTINCT(name) FROM ENSLIB_HL7.Message) m LEFT JOIN
        (
        SELECT CAST(li.SessionId AS Bigint) AS session_id, li.name, MIN(li.TimeCreated) AS SessionStart, MAX(lo.TimeCreated) AS SessionEnd, CAST(DATEDIFF(s, MIN(li.TimeCreated), MAX(lo.TimeCreated)) AS DECIMAL(5, 2)) AS ResponseTime
        FROM (SELECT h1.SessionId, h1.TimeCreated, $PIECE(RawContent, '|', 4), m1.name FROM ens.messageheader h1, ENSLIB_HL7.Message m1 WHERE h1.MessageBodyId = m1.id AND h1.TimeCreated > DATEADD(mi, -30, GETUTCDATE())) li
        JOIN (SELECT h2.SessionId, h2.TimeCreated FROM ens.messageheader h2, ENSLIB_HL7.Message m2 WHERE h2.MessageBodyId = m2.id AND h2.TimeCreated > DATEADD(mi, -30, GETUTCDATE())) lo
        ON li.SessionId = lo.SessionId
        GROUP BY li.SessionId
        ) l on m.name = l.name
GROUP BY l.Name

This gives me 4 results:

VXU_V04   0   (null)
ADT_A03   3   0.01
ADT_A04   3   0.01
ADT_A08   143 0.01

Given that there is one result with 0 records, it seems like it is working. However, if I run SELECT DISTINCT(name) FROM ENSLIB_HL7.Message I get 10 results:

VXU_V04
ADT_A08
ACK_A08
ADT_A03
ACK_A03
ADT_A04
ACK_A04
ACK_V04
ADT_A01
ACK_A01

Why don't I get ten rows with my full query?

Answer 1

Change the GROUP BY to:

GROUP BY m.Name

You are aggregating by the column in the second table, so you only get one row for all the NULL values.

Most databases would reject this syntax, but apparently Intersystems Cache allows it.