Mysql Query Select data on multiple rows

Question

I'm now doing four separate queries to select the information i need.

f.e.

SELECT value FROM data where sensor=123 AND value_id=a
SELECT value FROM data where sensor=123 AND value_id=b
SELECT value FROM data where sensor=123 AND value_id=c
SELECT value FROM data where sensor=123 AND value_id=d

Now i'm experementing with some code. I don't know if on my right way but is such a thing possible:

SELECT value180,value181,value182,value183
        FROM (
            SELECT
            MAX(CASE WHEN DATE(time) = subdate(CURDATE(), 1) THEN value ELSE 0 END) as value180,
            MAX(CASE WHEN DATE(time) = subdate(CURDATE(), 1) THEN value ELSE 0 END) as value181,
            MAX(CASE WHEN DATE(time) = subdate(CURDATE(), 1) THEN value ELSE 0 END) as value182,
            MAX(CASE WHEN DATE(time) = subdate(CURDATE(), 1) THEN value ELSE 0 END) as value183
            FROM data
            WHERE sensor_id = 1605850
            AND value_id IN ("1.8.0","1.8.1","1.8.2","1.8.3")
        ) a

It would be nice to have a single query...thx for helping in advance!

note: i have to use max value of each day per sensor per value. In the above first example this max function was just left out for simplification.

This is the table how it looks like:

as you can see there are hourly taken values for each value_id.

What i need: I need the highest value of yesterday for defined value_id's.

f.e. 1.8.0 = 3726.12, 1.8.1 = 663.69, ...

With my combined query i'm getting wrong values but the format how i want to get the values is correct:

Those two achieve very different things... maybe if you explain what you want to get and the tables you are getting it from — Alfabravo
– Alfabravo, Commented Nov 3, 2017 at 13:48
No i don't get an error but i'm only getting one and the same value for each value. (value180,value181,value182,value183 = all the same value). I need the value of a in value180 and so on ... — user3882511
– user3882511, Commented Nov 3, 2017 at 14:24

JBR · Accepted Answer · 2017-11-03 13:56:46Z

2

try

SELECT  value FROM data
WHERE sensor=123 AND value_id IN ('a', 'b', 'c' , 'd');

or

SELECT value FROM data
WHERE sensor=123 AND value_id IN (SELECT DISTINCT value_id FROM data);

answered Nov 3, 2017 at 13:56

JBR

13610 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

William Perron Over a year ago

I would also add a GROUP BY option

XING Over a year ago

This is not the correct answer to the question. Plesae read it once again.

JBR Over a year ago

he specified that ` I don't know if on my right way ` and now he is doing with 4 line of code. My answer is for that.

user3882511 Over a year ago

I need exact four values back therefore i can fetch them in php. -> value180,value181,value182,value183. This solution bring only one value back with multiple entries.

JBR Over a year ago

please add your table and required fields

user3882511 · Accepted Answer · 2017-11-03 16:26:58Z

After a short walk through the nature to take a pause i was able to solve my problem.

SELECT
(SELECT MAX(CASE WHEN DATE(time) = subdate(CURDATE(), 1) THEN value ELSE 0 END) FROM data WHERE sensor_id = 1605850 AND value_id = "1.8.0") as value180,
(SELECT MAX(CASE WHEN DATE(time) = subdate(CURDATE(), 1) THEN value ELSE 0 END) FROM data WHERE sensor_id = 1605850 AND value_id = "1.8.1") as value181,
(SELECT MAX(CASE WHEN DATE(time) = subdate(CURDATE(), 1) THEN value ELSE 0 END) FROM data WHERE sensor_id = 1605850 AND value_id = "1.8.2") as value182,
(SELECT MAX(CASE WHEN DATE(time) = subdate(CURDATE(), 1) THEN value ELSE 0 END) FROM data WHERE sensor_id = 1605850 AND value_id = "1.8.3") as value183

that was all.

Collectives™ on Stack Overflow

Mysql Query Select data on multiple rows

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related