Weird mysql query return valuesWhich MySQL data type to use for storing boolean valuesHow to output MySQL query results in CSV format?How do I connect to a MySQL Database in Python?Should I use the datetime or timestamp data type in MySQL?Finding duplicate values in MySQLHow to get a list of user accounts using the command line in MySQL?How to import CSV file to MySQL tablemysql -> insert into tbl (select from another table) and some default valuesHow to reset AUTO_INCREMENT in MySQL?How to import an SQL file using the command line in MySQL?
Biblical Basis for 400 years of silence between old and new testament
How did Vers know Agent Fury's name?
Why is A union B also called "A or B"?
How do I subvert the tropes of a train heist?
60s (or earlier) short story where each colony has one person who doesn't connect well with others who is there for being able to absorb knowledge
How to capture more stars?
Why would Lupin kill Pettigrew?
What does the word 「ごねだした」 mean?
Strange math syntax in old basic listing
Is it possible to kill all life on Earth?
Comment dit-on « I’ll tell you what » ?
What does "Marchentalender" on the front of a postcard mean?
How does one "dump" or deplete propellant without changing spacecraft attitude or trajectory?
How can I prevent interns from being expendable?
What is the difference between nullifying your vote and not going to vote at all?
Asking bank to reduce APR instead of increasing credit limit
Infinitely many hats
Mark as deprecated function parameters in C++14
What are the slash markings on Gatwick's 08R/26L?
Is this light switch installation safe and legal?
Preserving culinary oils
How can I grammatically understand "Wir über uns"?
Can a non-EU citizen travel within the Schengen area without identity documents?
US entry in Atlanta airport (ATL) together with a US citizen
Weird mysql query return values
Which MySQL data type to use for storing boolean valuesHow to output MySQL query results in CSV format?How do I connect to a MySQL Database in Python?Should I use the datetime or timestamp data type in MySQL?Finding duplicate values in MySQLHow to get a list of user accounts using the command line in MySQL?How to import CSV file to MySQL tablemysql -> insert into tbl (select from another table) and some default valuesHow to reset AUTO_INCREMENT in MySQL?How to import an SQL file using the command line in MySQL?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
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.
mysql
asked Mar 24 at 9:50
J.EwaJ.Ewa
508
add a comment |
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.
mysql
asked Mar 24 at 9:50
J.EwaJ.Ewa
508
What is your expected result?
– forpas
Mar 24 at 10:21
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
Mar 24 at 15:54
add a comment |
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.
mysql
asked Mar 24 at 9:50
J.EwaJ.Ewa
508
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.
mysql
mysql
asked Mar 24 at 9:50
J.EwaJ.Ewa
508
asked Mar 24 at 9:50
J.EwaJ.Ewa
508
asked Mar 24 at 9:50
J.EwaJ.Ewa
508
asked Mar 24 at 9:50
J.EwaJ.Ewa
508
asked Mar 24 at 9:50
J.EwaJ.Ewa
508
508
What is your expected result?
– forpas
Mar 24 at 10:21
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
Mar 24 at 15:54
add a comment |
What is your expected result?
– forpas
Mar 24 at 10:21
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
Mar 24 at 15:54
What is your expected result?
– forpas
Mar 24 at 10:21
What is your expected result?
– forpas
Mar 24 at 10:21
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
Mar 24 at 15:54
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
Mar 24 at 15:54
add a comment |
1 Answer
1
active
oldest
votes
The second return 10 because you have insert 10 rows in table robot_data
the first return 24 because your sintax
FROM ( ) temp, ( ) hum
produce a cross join between the table returned by subquery from temperature of 4 rows and table return from sunquery for humidity of 6 rows so 6x4 = 24 rows
answered Mar 24 at 10:22
scaisEdgescaisEdge
102k105472
Ok. This was insightful and I managed to solve my problem by using left joins.
– J.Ewa
Mar 25 at 9:43
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55322517%2fweird-mysql-query-return-values%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
The second return 10 because you have insert 10 rows in table robot_data
the first return 24 because your sintax
FROM ( ) temp, ( ) hum
produce a cross join between the table returned by subquery from temperature of 4 rows and table return from sunquery for humidity of 6 rows so 6x4 = 24 rows
answered Mar 24 at 10:22
scaisEdgescaisEdge
102k105472
Ok. This was insightful and I managed to solve my problem by using left joins.
– J.Ewa
Mar 25 at 9:43
add a comment |
The second return 10 because you have insert 10 rows in table robot_data
the first return 24 because your sintax
FROM ( ) temp, ( ) hum
produce a cross join between the table returned by subquery from temperature of 4 rows and table return from sunquery for humidity of 6 rows so 6x4 = 24 rows
answered Mar 24 at 10:22
scaisEdgescaisEdge
102k105472
Ok. This was insightful and I managed to solve my problem by using left joins.
– J.Ewa
Mar 25 at 9:43
add a comment |
The second return 10 because you have insert 10 rows in table robot_data
the first return 24 because your sintax
FROM ( ) temp, ( ) hum
produce a cross join between the table returned by subquery from temperature of 4 rows and table return from sunquery for humidity of 6 rows so 6x4 = 24 rows
answered Mar 24 at 10:22
scaisEdgescaisEdge
102k105472
The second return 10 because you have insert 10 rows in table robot_data
the first return 24 because your sintax
FROM ( ) temp, ( ) hum
produce a cross join between the table returned by subquery from temperature of 4 rows and table return from sunquery for humidity of 6 rows so 6x4 = 24 rows
answered Mar 24 at 10:22
scaisEdgescaisEdge
102k105472
answered Mar 24 at 10:22
scaisEdgescaisEdge
102k105472
answered Mar 24 at 10:22
scaisEdgescaisEdge
102k105472
answered Mar 24 at 10:22
scaisEdgescaisEdge
102k105472
102k105472
Ok. This was insightful and I managed to solve my problem by using left joins.
– J.Ewa
Mar 25 at 9:43
add a comment |
Ok. This was insightful and I managed to solve my problem by using left joins.
– J.Ewa
Mar 25 at 9:43
Ok. This was insightful and I managed to solve my problem by using left joins.
– J.Ewa
Mar 25 at 9:43
Ok. This was insightful and I managed to solve my problem by using left joins.
– J.Ewa
Mar 25 at 9:43
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55322517%2fweird-mysql-query-return-values%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
What is your expected result?
– forpas
Mar 24 at 10:21
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
Mar 24 at 15:54