MySQL - Combine rows with different where statements

Question

I have two MySQL Querys i would like to combine.

SELECT DEVICE as Room, VALUE as Temperature FROM `current` WHERE `READING` LIKE 'temperature'
SELECT DEVICE as Room, VALUE as Humidity    FROM `current` WHERE `READING` LIKE 'humidity'

My Data Table looks like this:

DEVICE	VALUE	READING
Arbeitszimmer	52	humidity
Arbeitszimmer	22	temperature
Aussen	88	humidity
Aussen	1.8	temperature

The desired result should be:

Room	Temperature	Humidity
Arbeitszimmer	22	52
Aussen	1.8	88

I am struggeling with the SQL Query.

score 0 · Accepted Answer · edited Nov 27 '21 at 16:40

0

Simple self join should work

SELECT c1.DEVICE as Room, c1.VALUE as Temperature, c2.VALUE as Humidity  
FROM `current` c1 
    join `current` c2 on c1.device = c2.device 
        and c1.`READING` = 'temperature'
        and c2.`READING` = 'humidity'

BTW there is no need to use like

edited Nov 27 '21 at 16:40

RiggsFolly

93,638
21
103
149

answered Nov 27 '21 at 16:38

Jens

67,715
15
98
113

score 0 · Answer 2 · answered Nov 27 '21 at 16:39

0

if you have only two classs of values you could use a join with the same table

select a.device, a.value temp, b.value humidity
from current a  
inner join current a.device = b.device 
    and a.reading = 'temperature'
        and b.reading = 'humidity'

answered Nov 27 '21 at 16:39

ScaisEdge

131,976
10
91
107

score 0 · Answer 3 · answered Nov 27 '21 at 16:49

A bit of conditional aggregation to pivot the READING metrics.

SELECT `DEVICE` as Room
, MAX(CASE WHEN `READING` = 'temperature' THEN `VALUE` END) as Temperature
, MAX(CASE WHEN `READING` = 'humidity' THEN `VALUE` END) as Humidity
FROM `current` 
WHERE `READING` IN ('temperature', 'humidity') 
GROUP BY `DEVICE`
ORDER BY `DEVICE`

MySQL - Combine rows with different where statements

3 Answers3