B.W B.W - 6 months ago 7
SQL Question

Query to get values from table based on criteria from same table, Mysql

I have a MySQL table with the following table structure and desired output

historical_id grd_id register_type timestamp address value historical_type insertion_time
5358 2 11 2016-05-07 12:45:00 1 18.1 1 2016-05-07 13:44:58
5359 2 11 2016-05-07 12:45:00 2 51.4 1 2016-05-07 13:44:58
5360 2 11 2016-05-07 12:45:00 3 476 1 2016-05-07 13:44:58
5364 2 11 2016-05-07 13:00:00 1 18.79 1 2016-05-07 13:59:58
5365 2 11 2016-05-07 13:00:00 2 51.2 1 2016-05-07 13:59:58
5366 2 11 2016-05-07 13:00:00 3 718 1 2016-05-07 13:59:58


Desired output from query

kWh_date temp rh co2
2016-05-0712:45:00 18.1 51.4 476
2016-05-0713:00:00 18.79 51.2 718


Can anybody help as to how I get the desired output. I have tried to 'GROUP' by timestamp (not my decision to use this name and cannot change it) but it just shows me 1 set of data (ie 'address' 1). I have used 3 'SELECT' statements but that shows the timestamp repeated. Any help is much appreciated.

I have tried

SELECT `register_type`
, `timestamp`
, `address`
, `value`
FROM grdxf.historical
WHERE `register_type` = 11
GROUP
BY `timestamp`;


I have tried different queries but they end up failing also. I'm really at a lose and new to queries.

Answer

E.g.:

SELECT FROM_UNIXTIME(900 * FLOOR(UNIX_TIMESTAMP(timestamp)/900)) timestamp
     , MAX(CASE WHEN address = 1 THEN value END) temp 
     , MAX(CASE WHEN address = 2 THEN value END) rh 
     , MAX(CASE WHEN address = 3 THEN value END) co2 
  FROM historical
 WHERE register_type = 11 
 GROUP 
    BY timestamp;
Comments