1

I am working with 2 tables and need help to produce an output by converting rows to columns, and i need to sum the value first be grouping

Here is the fiddle: https://www.db-fiddle.com/f/kmQjRvvensRTfYsSELxMF2/1

Here is the table:

CREATE TABLE teacher (
TeacherId INT, BranchId VARCHAR(5));
INSERT INTO teacher VALUES
("1121","A"),
("1132","A"),
("1141","A"),
("2120","B"), 
("2122","B"),
("2123","B");
                               
CREATE TABLE activities (
ID INT, TeacherID INT,    Hours   INT);

INSERT INTO activities VALUES
(1,1121,2),
(2,1121,1),
(3,1132,1),
(4,1141,NULL),
(5,2120,NULL),
(6,2122,NULL),
(7,2123,2),
(7,2123,2);

My SQL:

    SELECT totalhours hours
             , branchid
             , COUNT(*) total
          FROM 
             ( SELECT COALESCE(y.hr,0) totalhours
                    , x.branchid
                    , x.teacherid
                 FROM teacher x
                 JOIN 
                    ( SELECT teacherid
                           , SUM(hours) hr 
                        FROM activities
                       GROUP 
                          BY teacherid
                       ORDER 
                          BY hr ASC
                    ) y
                   ON x.teacherid = y.teacherid
             ) a
         GROUP 
            BY hours
             , branchid
         ORDER
            BY hours
             , branchid;

Output:

   +---------------+-------------------+--------------------+
   |     hours     |     branchid      |       total        |
   +---------------+-------------------+--------------------+
   |       0       |        A          |         1          |
   |       0       |        B          |         2          |
   |       1       |        A          |         1          |
   |       3       |        A          |         1          |
   |       4       |        B          |         1          |
   +---------------+-------------------+--------------------+

Explanation:

Table teacher consist teacher id and branch id, while table activities consist of id, foreign key teacher id, and hours. Hours indicate duration of each activities made by teacher. Teacher can do more than one activities or may not do any activities. Teachers who not doing any activity will be set to null.

The objective of queries is to produce a table that consist of summary of teachers activity by branch and group by hours.

In the expected output table, 'Hours' is a fixed value to indicate hours from in ascending order starting from 0 to 12. It will still display value even there are no hours value for A and B. A and B columns are branch. The value indicates total number of teachers who are doing activities. So, for row 0, there are 1 teacher for branch A and 2 teachers for branch B who are not doing activities.

Expected output:

   +-----------+------------+------------+
   |   Hours   |     A      |     B      |
   +-----------+------------+------------+
   |     0     |     1      |     2      |
   |     1     |     1      |     0      |
   |     2     |     0      |     0      |
   |     3     |     1      |     0      |
   |     4     |     0      |     1      |
   +-----------+------------+------------+
  • Follow the [pivot] tag. – Rick James Jul 3 at 3:06
0
SELECT hours, SUM(CASE WHEN branchid = 'A'
                       THEN total
                       ELSE 0 END) A, SUM(CASE WHEN branchid = 'B'
                                               THEN total
                                               ELSE 0 END) B
FROM output
GROUP BY hours;

or

SELECT hours, SUM(CASE WHEN branchid = 'A'
                       THEN total
                       ELSE 0 END) A, SUM(CASE WHEN branchid = 'B'
                                               THEN total
                                               ELSE 0 END) B
FROM (SELECT 0 hours UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) hours_table
LEFT JOIN output USING (hours)
GROUP BY hours;

fiddle

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.