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 |
+-----------+------------+------------+
Best Answer
or
fiddle