I have a table named runs with the following values.

I am trying to get an output of number of records per hour for each 'o_id'.

This sql query: SELECT HOUR(start_date) AS HOUR, COUNT('o_id') AS lo FROM runs WHERE start_date BETWEEN str_to_date('2021-12-30 02', '%Y-%m-%d %H') AND str_to_date('2021-12-31 02', '%Y-%m-%d %H') AND o_id = 'lo' GROUP BY HOUR(start_date) ORDER BY HOUR(start_date);

returns:

And I would like to get a single query to return output like this:

I have tried:

SELECT HOUR(start_date) AS HOUR, COUNT('o_id') AS vi
        FROM runs W
        WHERE W.start_date BETWEEN str_to_date('2021-12-30 02', '%Y-%m-%d %H') 
        AND str_to_date('2021-12-31 02', '%Y-%m-%d %H') 
        AND W.o_id = 'vi'
UNION SELECT HOUR(start_date) AS HOUR, COUNT('o_id') AS lo
        FROM runs W
        WHERE W.start_date BETWEEN str_to_date('2021-12-30 02', '%Y-%m-%d %H') 
        AND str_to_date('2021-12-31 02', '%Y-%m-%d %H') 
        AND W.orchestrator_id = 'lo'

But I have obviously misunderstood how this works. I feel like I need to Left Join, but I cant think how to get it to work in my particular instance. Any advice?

CodePudding user response：

You can add a condition inside your COUNT():

SELECT HOUR(start_date) AS HOUR,
COUNT(case when o_id = 'lo' then 1 end) AS lo,
COUNT(case when o_id = 'vi' then 1 end) AS vi
FROM runs 
WHERE start_date BETWEEN str_to_date('2021-12-30 02', '%Y-%m-%d %H') AND str_to_date('2021-12-31 02', '%Y-%m-%d %H')
GROUP BY HOUR(start_date) 
ORDER BY HOUR(start_date);

Fiddle

CodePudding user response：

A UNION query cannot solve your issue as it will combine the rows of 2 queries. To "combine the columns" you can use a JOIN but this should not be necessary for your use case.

Using the SUM function with a CASE WHEN:

SELECT HOUR(start_date) AS hour
    , SUM(CASE WHEN o_id = 'lo' THEN 1 ELSE 0 END) AS lo
    , SUM(CASE WHEN o_id = 'vi' THEN 1 ELSE 0 END) AS vi
FROM runs
WHERE start_date BETWEEN str_to_date('2021-12-30 02', '%Y-%m-%d %H')
    AND str_to_date('2021-12-31 02', '%Y-%m-%d %H')
GROUP BY hour
ORDER BY hour;

I also replaced the GROUP BY & ORDER BY clauses to reuse the hour alias.