why is this SQL query failing to run? SELECT gs.h hours, severity."alarmSeverity", COUNT(t1.eventtime) 
     FROM GENERATE_SERIES(0, 23, 1) gs(h) 
     CROSS JOIN (SELECT DISTINCT t1."alarmSeverity" 
                FROM (SELECT eventdate, eventtime, "alarmSeverity", eventposition, eventtype FROM alarm1 UNION 
                      SELECT eventdate, eventtime, "alarmSeverity", eventposition, eventtype FROM alarm2 UNION 
                      SELECT eventdate, eventtime, "alarmSeverity", eventposition, eventtype FROM alarm3)t1 
                      WHERE t1."alarmSeverity" != 0) AS severity
     LEFT JOIN (t1 ON extract(hour FROM t1.eventtime) = gs.h AND t1."alarmSeverity" = severity."alarmSeverity" 
     AND t1.eventdate+t1.eventtime BETWEEN TIMESTAMP '2019-07-01T18:30:00.000Z' AND TIMESTAMP '2019-07-02T18:29:59.999Z' 
     GROUP BY gs.h, severity."alarmSeverity" 
     ORDER BY gs.h, severity."alarmSeverity";


this is supposed to give a list of hours from 0-23 along with the count of alarms triggered grouped by severity like

    {
        "hours": 0,
        "alarmSeverity": 1,
        "count": "0"
    },
    {
        "hours": 0,
        "alarmSeverity": 2,
        "count": "0"
    },
    {
        "hours": 0,
        "alarmSeverity": 3,
        "count": "0"
    },...


the error is

Query 1 ERROR: ERROR:  syntax error at or near "ON"
LINE 14:      LEFT JOIN (t1 ON extract(hour FROM t1.eventtime) = gs.h...
1st : We JOIN tables/view or sub-query that returns a result-set.

2nd : ON condition is used to get the result matching given condition.
      Note: CROSS JOIN don't need ON condition.

I tried to correct you query. Hope this query will help you out to get desired result.

WITH CTE AS

(SELECT eventdate, eventtime, "alarmSeverity", eventposition, eventtype FROM alarm1 UNION 
SELECT eventdate, eventtime, "alarmSeverity", eventposition, eventtype FROM alarm2 UNION 
SELECT eventdate, eventtime, "alarmSeverity", eventposition, eventtype FROM alarm3
)

SELECT gs.h hours, severity."alarmSeverity", COUNT(CTE.eventtime) 
     FROM GENERATE_SERIES(0, 23, 1) gs(h)
     CROSS JOIN (SELECT DISTINCT "alarmSeverity"
                    FROM CTE
                    WHERE "alarmSeverity" != 0
                ) AS severity
LEFT JOIN CTE
ON          extract(hour FROM CTE.eventtime) = gs.h AND CTE."alarmSeverity" = severity."alarmSeverity" 
            AND CTE.eventdate+CTE.eventtime BETWEEN TIMESTAMP '2019-07-01T18:30:00.000Z' AND TIMESTAMP '2019-07-02T18:29:59.999Z' 
GROUP BY gs.h, severity."alarmSeverity" 
ORDER BY gs.h, severity."alarmSeverity";


Thanks!