I am trying to create a crosstab that gives me a count of crash type by injury severity, and the data is in three tables (crashes, occupants & pedestrians). The occupants & pedestrians tables have a many-to-one relationship with crashes since there is typically more than one person involved in a crash. I made a view to handle assigning the crash severity to each crash_id based on the occupants and pedestrians tables. Now when I run the query below, the total count is correct, but the counts in each category are not always correct.

SELECT *
FROM crosstab(
    'SELECT CONCAT(ct.crash_type_code, '' - '', ct.crash_type_desc) AS crash_type, 
            cs.crash_severity, 
            COALESCE(count(cs.crash_severity), 0) AS count 
    FROM (
        SELECT DISTINCT crash_type_code, crash_type_desc FROM crash_type_lookup
    ) ct
    CROSS JOIN (
        SELECT DISTINCT crash_severity FROM crash_severity
    ) cs
    LEFT JOIN crash_severity ON cs.crash_severity = crash_severity.crash_severity
                              AND crash_severity.crash_type = ct.crash_type_code
    GROUP BY ct.crash_type_code, ct.crash_type_desc, cs.crash_severity
    ORDER BY 1,2'
)
AS CrashType(crash_type text,
            "01 - Fatal Injury" bigint,
            "02 - Suspected Serious Injury" bigint,
            "03 - Suspected Minor Injury" bigint,
            "04 - Possible Injury" bigint,
            "Property Damage Only" bigint);

Will return this:

But row 1, the 00 – Unknown category, should be null for the first two columns (Fatal Injury and Suspected Serious Injury) and 1, 1, 24 for the last three columns. So it seems it’s just not accounting for the fact that the first two columns are null, and I can’t figure out what I’m doing wrong. Here is a sample of the data showing how the 00 – Unknown crash types should be distributed.

Other areas are probably incorrect also, I haven’t checked everything since its obvious it’s not working right. Any help would be much appreciated.