MySQL Count and Convert Row to Colum Involve One Table Only-CodePudding

I have a table name histories that record user activities which consists of user_id, branch_id and duration.

The table look like this

                                   
id | user_id | branch_id | totHours
                                   
|1 |   100   |     1     |    1   |
|2 |   199   |     1     |    1   |
|3 |   121   |     1     |    1   |
|4 |   140   |     1     |    1   |
|5 |   103   |     2     |    3   |
|6 |   107   |     2     |    1   |
|7 |   299   |     1     |    2   |
|8 |   209   |     2     |    2   |
|9 |   119   |     1     |    5   |

I would like to produce an output like this:

                           
Hours | Branch A | Branch B
                           
|1    |    4    |     1   |
|2    |    1    |     1   |
|3    |    0    |     1   |
|4    |    0    |     0   |
|5    |    1    |     0   |

I try make it using this query, but when i use group by on totHours column only, it return error because i need to include the branch_id in the group by.

Here is my query:

select totHours as Hours,
   coalesce(case when branch_id = 1 then count(totHours) else 0 end) as 'Branch A',
   coalesce(case when branch_id = 2 then count(totHours) else 0 end) as 'Branch B'
from histories
group by totHours, branch_id;

And if the totHours is not in the table (for example in this table 4), it will display 0 for both branch column.

Here is my db fiddle

Update: MySQL version 5.7.22

Thanks

CodePudding user response：

If you're using MySQL version 8 (or any version support windows function), you can make use of the recursive common table expression to generate the hour values for you then LEFT JOIN table histories with it. After that you can do SUM() with CASE expression in SELECT to generate your expected output:

WITH RECURSIVE hours AS (
    SELECT 1 AS hr, MAX(totHours) AS maxth FROM histories UNION ALL
    SELECT hr 1, maxth FROM hours WHERE hr 1 <= maxth)
SELECT hours.hr, 
       SUM(CASE WHEN histories.branch_id=1 THEN 1 ELSE 0 END) AS Branch_A,
       SUM(CASE WHEN histories.branch_id=2 THEN 1 ELSE 0 END) AS Branch_B
FROM hours
 LEFT JOIN histories 
 ON hours.hr=histories.totHours
GROUP BY hours.hr;

If you're using version that doesn't support window function, you can create a subquery to represent the hours (including missing hour). This is a hard-coding approach where you may have to always update the subquery to include new hour value (if any):

SELECT hours.hr, 
       SUM(CASE WHEN histories.branch_id=1 THEN 1 ELSE 0 END) AS Branch_A,
       SUM(CASE WHEN histories.branch_id=2 THEN 1 ELSE 0 END) AS Branch_B
FROM 
    (SELECT 1 hr UNION 
      SELECT 2 UNION 
      SELECT 3 UNION 
      SELECT 4 UNION 
      SELECT 5) AS hours
 LEFT JOIN histories 
 ON hours.hr=histories.totHours
GROUP BY hours.hr;

Demo fiddle

Edit the hours subquery to add more, for example if you want until 7, you just add:

(SELECT 1 hr UNION 
      SELECT 2 UNION 
      SELECT 3 UNION 
      SELECT 4 UNION 
      SELECT 5 UNION 
      SELECT 6 UNION 
SELECT 7) AS hours

to the subquery. Another way is to define the hours beforehand and create a reference table. Let's say you estimate the hour to be until 100, then it's better if you create a table that stores 1-100 as reference for the LEFT JOIN