I have a table of data recording new downtime reasons every hour for all machines with the following format:
ID
t_stamp
machine
reason
minutes
3
2023-01-22 08:00:00
Solder1
Running
60
4
2023-01-22 08:00:00
Solder2
Running
60
5
2023-01-22 08:00:00
Overdraw32
Running
60
6
2023-01-22 08:00:00
Overdraw50
Running
60
7
2023-01-22 08:00:00
Overdraw60
Running
60
8
2023-01-22 09:00:00
Solder1
Stopped
60
9
2023-01-22 09:00:00
Solder2
Stopped
60
10
2023-01-22 09:00:00
Overdraw32
Stopped
60
11
2023-01-22 09:00:00
Overdraw50
Stopped
60
12
2023-01-22 09:00:00
Overdraw60
Stopped
60
13
2023-01-22 10:00:01
Solder1
Stopped
60
14
2023-01-22 10:00:01
Solder2
Stopped
60
15
2023-01-22 10:00:01
Overdraw32
Stopped
60
16
2023-01-22 10:00:01
Overdraw50
Stopped
60
17
2023-01-22 10:00:01
Overdraw60
Stopped
60
I would like a query that can return the daily sums of the minutes for each reason and make a column for each distinct reason like this (To work with a bar chart I think I would have to have to return only the data for 1 machine at a time - so this would be for only 1 machine so I think I need to loop through the distinct reasons and then use those reasons as the header name for my query):
That is called a PIVOT. Your DB can do it for you. You can script it for a bit more flexibility. Even my view() expression function in Simulation Aids can do this.
SELECT t_stamp as time,
SUM(CASE WHEN reason = 'Running' THEN minutes ELSE 0 END) AS Running,
SUM(CASE WHEN reason = 'Stopped' THEN minutes ELSE 0 END) AS Stopped
FROM downtimeTracking
GROUP BY t_stamp;
There won't always just be Running and Stopped, and there could also be a custom input that I can't predict so the query needs to be able to loop through a distinct unknown number of reasons too.
I was able to do it in an actual MySQL database using the GROUP_CONCAT function.
Here is the query:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'SUM(CASE WHEN reason = ''',
reason,
''' THEN minutes ELSE 0 END) AS `',
reason, '`'
)
) INTO @sql
FROM test_schema.downtimeTracking;
SET @sql = CONCAT('SELECT t_stamp as time, ', @sql, ' FROM test_schema.downtimeTracking GROUP BY t_stamp');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Note: My database name is test_schema, and my table name is downtimetracking. Obviously, these will need to be changed to match the names of your schema and table. Otherwise, the function should work as expected.