Weird mysql query return values

Question

I am trying to sum values from a tables column where some conditions are met but I can't figure out the returned values from mysql.

I have run the below script.

create table robot_data(name varchar(20), value float, x int, y int, z int);


insert into robot_data(name, value, x, y, z) values("temperature", 27.99, 1, 88, 0);
insert into robot_data(name, value, x, y, z) values("temperature", 27.99, 1, 88, 0);
insert into robot_data(name, value, x, y, z) values("temperature", 27.99, 1, 88, 0);
insert into robot_data(name, value, x, y, z) values("temperature", 27.99, 1, 88, 0);

insert into robot_data(name, value, x, y, z) values("humidity", 7.99, 1, 88, 0);
insert into robot_data(name, value, x, y, z) values("humidity", 7.99, 1, 88, 0);
insert into robot_data(name, value, x, y, z) values("humidity", 7.99, 1, 88, 0);
insert into robot_data(name, value, x, y, z) values("humidity", 7.99, 1, 88, 0);
insert into robot_data(name, value, x, y, z) values("humidity", 7.99, 1, 88, 0);
insert into robot_data(name, value, x, y, z) values("humidity", 7.99, 1, 88, 0);




SELECT  COUNT(temp.value) FROM 

(SELECT value, name from robot_data where name ='temperature') as temp,
(SELECT value, name from robot_data where name ='humidity') as hum;

SELECT COUNT(value) FROM robot_data;

I expect the first and second select to return the same results but they don't. The first one returns 24 and the second one 10 which is correct.

In reality what I want to do is this in the first select:

SELECT ROUND(SUM(temp.value + hum.value))...

But I want none matching rows to return null since the values for temperature are 4 and humidity 6. I will appreciate if I can get an insight on what is happening.

I have two expectations the first one is in the question but the answer below has clarified that it is essentially a table join this I did not know. The second one is to sum matching rows in temperature and humidity and null for rows without matching values so I will have 6 rows with the first four summing temp and humidity and the last one null. — J.Ewa
– J.Ewa, Commented Mar 24, 2019 at 15:54

halfer · Accepted Answer · 2022-06-10 19:03:00Z

2

The second return 10 because you have insert 10 rows in table robot_data

The first return 24 because your syntax

FROM ( ) temp, ( ) hum

produces a cross join between the table returned by subquery from temperature of 4 rows and table return from subquery for humidity of 6 rows. So 6x4 = 24 rows.

edited Jun 10, 2022 at 19:03

halfer

20.2k20 gold badges110 silver badges207 bronze badges

answered Mar 24, 2019 at 10:22

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

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

1 Comment

J.Ewa Over a year ago

Ok. This was insightful and I managed to solve my problem by using left joins.

Collectives™ on Stack Overflow

Weird mysql query return values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related