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.

7

Thanks Justin,

I am using Mysql and was able to use this but I have a couple questions. It doesn't seem to give me the last column name as Stopped, any idea how to fix that?
And it doesn't work as a named query in Ignition, any idea how to get around that?

image
image1196×152 8.33 KB

8

It would be so much simpler if mySql had a built in PIVOT function, but for this case, one approach would be to parse the alias from the field name of the last column.

EDIT: I realized that the actual problem is the default GROUP_CONCAT() limit of 1024 bytes. Miraculously, it appears that the break point in your usage case was actually at a point that would return a result instead of an error.

Simply add this to first line of the original procedure I posted, and it will correct the issue:

SET SESSION group_concat_max_len = 1000000;
9

Save it in the database as a stored procedure and then Call the stored procedure from Ignition.

11

SET @sql = CONCAT('SELECT CAST(t_stamp AS DATE) AS time, ', @sql, ' FROM schema.downtime_minutes WHERE t_stamp > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 2 DAY) GROUP BY CAST(t_stamp AS DATE)');

This line seems to work, but if I want to pass in like a @start and @end it won't let me do that.

EDIT:
SET @sql = CONCAT('SELECT CAST(t_stamp AS DATE) AS time, ', @sql, ' FROM schema.downime_minutes WHERE t_stamp BETWEEN "',@start, '" AND "',@end, '" GROUP BY CAST(t_stamp AS DATE)');

This works.

12

I just checked this with live data, and it seems as though I am not selecting on a single machine.
By your example the 300 running and 300 stopped minutes per hour is totalizing all 5 machines.

13

Understood. To add machine info to the pivot and break up the results, simply add machine to your SELECT statement and GROUP BY clause:

SET @sql = CONCAT('SELECT machine, CAST(t_stamp AS DATE) AS time, ', @sql, ' FROM luvata.downtime_minutes WHERE t_stamp BETWEEN "',@start, '" AND "',@end, '" GROUP BY machine, CAST(t_stamp AS DATE)');