Select variable number of columns with MYSQL

Ignition
#1

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):

time Running Stopped
2023-01-22 60 120
#2

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.

Numerous explanatory topics here.

#3

I was able to accomplish this with SUM(CASE):

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;

Here is the result:

image
image1088×156 5.68 KB

#4

Thanks.

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.

#5

What database are you using?

#6

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;

Here is the result:

image
image1059×488 32.9 KB

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.