confused mysql answers do not add up to same number
up vote
-1
down vote
favorite
i have a call to get the count(s) of players at our stores, the overall total is less than the separate totals added up. i am at a loss as to why this is happening.
SELECT (SELECT
COUNT(DISTINCT (playerid))
FROM playerhistory
WHERE MONTH(from_unixtime(checkouttime)) = 11
AND YEAR(from_unixtime(checkouttime)) = 2018
AND storeid > 100
AND howmuchplayed > 0)
AS total,
(SELECT
COUNT(DISTINCT (playerid))
FROM playerhistory
WHERE MONTH(from_unixtime(checkouttime)) = 11
AND YEAR(from_unixtime(checkouttime)) = 2018
AND storeid = 12786
AND howmuchplayed > 0)
AS ra,
(SELECT
COUNT(DISTINCT (playerid))
FROM playerhistory
WHERE MONTH(from_unixtime(checkouttime)) = 11
AND YEAR(from_unixtime(checkouttime)) = 2018
AND storeid = 12801
AND howmuchplayed > 0)
AS mp,
(SELECT
COUNT(DISTINCT (playerid))
FROM playerhistory
WHERE MONTH(from_unixtime(checkouttime)) = 11
AND YEAR(from_unixtime(checkouttime)) = 2018
AND storeid = 12830
AND howmuchplayed > 0)
AS cy,
(SELECT
COUNT(DISTINCT (playerid))
FROM playerhistory
WHERE MONTH(from_unixtime(checkouttime)) = 11
AND YEAR(from_unixtime(checkouttime)) = 2018
AND storeid = 13389
AND howmuchplayed > 0)
AS ne,
(SELECT
COUNT(DISTINCT (playerid))
FROM playerhistory
WHERE MONTH(from_unixtime(checkouttime)) = 11
AND YEAR(from_unixtime(checkouttime)) = 2018
AND storeid = 13600
AND howmuchplayed > 0)
AS lm,
(SELECT
COUNT(DISTINCT (playerid))
FROM playerhistory
WHERE MONTH(from_unixtime(checkouttime)) = 11
AND YEAR(from_unixtime(checkouttime)) = 2018
AND storeid = 13694
AND howmuchplayed > 0)
AS ch
which gives the results of:
total | ra | mp | cy | ne | lm | ch
303 | 34 | 74 | 27 | 43 | 75 | 53
as you can see the total count is 303 but when you add up ra,mp,cy,ne,lm and ch it comes out to 306
there are no other storeids in the table:
select distinct(storeid) from playerhistory where storeid > 100
results in:
storeid
12786
12801
12830
13389
13600
13694
do i have a flaw in my query? why does it not add up?
thanks for any and all help with this.
mysql mysqli phpmyadmin
edited Nov 11 at 16:20
rsjaffe
3,42971431
asked Nov 11 at 16:09
wizeone
32
add a comment |
up vote
-1
down vote
favorite
i have a call to get the count(s) of players at our stores, the overall total is less than the separate totals added up. i am at a loss as to why this is happening.
SELECT (SELECT
COUNT(DISTINCT (playerid))
FROM playerhistory
WHERE MONTH(from_unixtime(checkouttime)) = 11
AND YEAR(from_unixtime(checkouttime)) = 2018
AND storeid > 100
AND howmuchplayed > 0)
AS total,
(SELECT
COUNT(DISTINCT (playerid))
FROM playerhistory
WHERE MONTH(from_unixtime(checkouttime)) = 11
AND YEAR(from_unixtime(checkouttime)) = 2018
AND storeid = 12786
AND howmuchplayed > 0)
AS ra,
(SELECT
COUNT(DISTINCT (playerid))
FROM playerhistory
WHERE MONTH(from_unixtime(checkouttime)) = 11
AND YEAR(from_unixtime(checkouttime)) = 2018
AND storeid = 12801
AND howmuchplayed > 0)
AS mp,
(SELECT
COUNT(DISTINCT (playerid))
FROM playerhistory
WHERE MONTH(from_unixtime(checkouttime)) = 11
AND YEAR(from_unixtime(checkouttime)) = 2018
AND storeid = 12830
AND howmuchplayed > 0)
AS cy,
(SELECT
COUNT(DISTINCT (playerid))
FROM playerhistory
WHERE MONTH(from_unixtime(checkouttime)) = 11
AND YEAR(from_unixtime(checkouttime)) = 2018
AND storeid = 13389
AND howmuchplayed > 0)
AS ne,
(SELECT
COUNT(DISTINCT (playerid))
FROM playerhistory
WHERE MONTH(from_unixtime(checkouttime)) = 11
AND YEAR(from_unixtime(checkouttime)) = 2018
AND storeid = 13600
AND howmuchplayed > 0)
AS lm,
(SELECT
COUNT(DISTINCT (playerid))
FROM playerhistory
WHERE MONTH(from_unixtime(checkouttime)) = 11
AND YEAR(from_unixtime(checkouttime)) = 2018
AND storeid = 13694
AND howmuchplayed > 0)
AS ch
which gives the results of:
total | ra | mp | cy | ne | lm | ch
303 | 34 | 74 | 27 | 43 | 75 | 53
as you can see the total count is 303 but when you add up ra,mp,cy,ne,lm and ch it comes out to 306
there are no other storeids in the table:
select distinct(storeid) from playerhistory where storeid > 100
results in:
storeid
12786
12801
12830
13389
13600
13694
do i have a flaw in my query? why does it not add up?
thanks for any and all help with this.
mysql mysqli phpmyadmin
edited Nov 11 at 16:20
rsjaffe
3,42971431
asked Nov 11 at 16:09
wizeone
32
1
Simplify. Format. Then try again.
– Strawberry
Nov 11 at 16:11
what do you mean "Simplify. Format" as in? (i made this query just to pull the totals to post here, the working code is a lot different)
– wizeone
Nov 11 at 16:18
thank you rsjaffe for fixing the layout i couldn't figure it out when i posted it(was my first post)
– wizeone
Nov 11 at 16:29
add a comment |
up vote
-1
down vote
favorite
up vote
-1
down vote
favorite
i have a call to get the count(s) of players at our stores, the overall total is less than the separate totals added up. i am at a loss as to why this is happening.
SELECT (SELECT
COUNT(DISTINCT (playerid))
FROM playerhistory
WHERE MONTH(from_unixtime(checkouttime)) = 11
AND YEAR(from_unixtime(checkouttime)) = 2018
AND storeid > 100
AND howmuchplayed > 0)
AS total,
(SELECT
COUNT(DISTINCT (playerid))
FROM playerhistory
WHERE MONTH(from_unixtime(checkouttime)) = 11
AND YEAR(from_unixtime(checkouttime)) = 2018
AND storeid = 12786
AND howmuchplayed > 0)
AS ra,
(SELECT
COUNT(DISTINCT (playerid))
FROM playerhistory
WHERE MONTH(from_unixtime(checkouttime)) = 11
AND YEAR(from_unixtime(checkouttime)) = 2018
AND storeid = 12801
AND howmuchplayed > 0)
AS mp,
(SELECT
COUNT(DISTINCT (playerid))
FROM playerhistory
WHERE MONTH(from_unixtime(checkouttime)) = 11
AND YEAR(from_unixtime(checkouttime)) = 2018
AND storeid = 12830
AND howmuchplayed > 0)
AS cy,
(SELECT
COUNT(DISTINCT (playerid))
FROM playerhistory
WHERE MONTH(from_unixtime(checkouttime)) = 11
AND YEAR(from_unixtime(checkouttime)) = 2018
AND storeid = 13389
AND howmuchplayed > 0)
AS ne,
(SELECT
COUNT(DISTINCT (playerid))
FROM playerhistory
WHERE MONTH(from_unixtime(checkouttime)) = 11
AND YEAR(from_unixtime(checkouttime)) = 2018
AND storeid = 13600
AND howmuchplayed > 0)
AS lm,
(SELECT
COUNT(DISTINCT (playerid))
FROM playerhistory
WHERE MONTH(from_unixtime(checkouttime)) = 11
AND YEAR(from_unixtime(checkouttime)) = 2018
AND storeid = 13694
AND howmuchplayed > 0)
AS ch
which gives the results of:
total | ra | mp | cy | ne | lm | ch
303 | 34 | 74 | 27 | 43 | 75 | 53
as you can see the total count is 303 but when you add up ra,mp,cy,ne,lm and ch it comes out to 306
there are no other storeids in the table:
select distinct(storeid) from playerhistory where storeid > 100
results in:
storeid
12786
12801
12830
13389
13600
13694
do i have a flaw in my query? why does it not add up?
thanks for any and all help with this.
mysql mysqli phpmyadmin
edited Nov 11 at 16:20
rsjaffe
3,42971431
asked Nov 11 at 16:09
wizeone
32
i have a call to get the count(s) of players at our stores, the overall total is less than the separate totals added up. i am at a loss as to why this is happening.
SELECT (SELECT
COUNT(DISTINCT (playerid))
FROM playerhistory
WHERE MONTH(from_unixtime(checkouttime)) = 11
AND YEAR(from_unixtime(checkouttime)) = 2018
AND storeid > 100
AND howmuchplayed > 0)
AS total,
(SELECT
COUNT(DISTINCT (playerid))
FROM playerhistory
WHERE MONTH(from_unixtime(checkouttime)) = 11
AND YEAR(from_unixtime(checkouttime)) = 2018
AND storeid = 12786
AND howmuchplayed > 0)
AS ra,
(SELECT
COUNT(DISTINCT (playerid))
FROM playerhistory
WHERE MONTH(from_unixtime(checkouttime)) = 11
AND YEAR(from_unixtime(checkouttime)) = 2018
AND storeid = 12801
AND howmuchplayed > 0)
AS mp,
(SELECT
COUNT(DISTINCT (playerid))
FROM playerhistory
WHERE MONTH(from_unixtime(checkouttime)) = 11
AND YEAR(from_unixtime(checkouttime)) = 2018
AND storeid = 12830
AND howmuchplayed > 0)
AS cy,
(SELECT
COUNT(DISTINCT (playerid))
FROM playerhistory
WHERE MONTH(from_unixtime(checkouttime)) = 11
AND YEAR(from_unixtime(checkouttime)) = 2018
AND storeid = 13389
AND howmuchplayed > 0)
AS ne,
(SELECT
COUNT(DISTINCT (playerid))
FROM playerhistory
WHERE MONTH(from_unixtime(checkouttime)) = 11
AND YEAR(from_unixtime(checkouttime)) = 2018
AND storeid = 13600
AND howmuchplayed > 0)
AS lm,
(SELECT
COUNT(DISTINCT (playerid))
FROM playerhistory
WHERE MONTH(from_unixtime(checkouttime)) = 11
AND YEAR(from_unixtime(checkouttime)) = 2018
AND storeid = 13694
AND howmuchplayed > 0)
AS ch
which gives the results of:
total | ra | mp | cy | ne | lm | ch
303 | 34 | 74 | 27 | 43 | 75 | 53
as you can see the total count is 303 but when you add up ra,mp,cy,ne,lm and ch it comes out to 306
there are no other storeids in the table:
select distinct(storeid) from playerhistory where storeid > 100
results in:
storeid
12786
12801
12830
13389
13600
13694
do i have a flaw in my query? why does it not add up?
thanks for any and all help with this.
mysql mysqli phpmyadmin
mysql mysqli phpmyadmin
edited Nov 11 at 16:20
rsjaffe
3,42971431
asked Nov 11 at 16:09
wizeone
32
edited Nov 11 at 16:20
rsjaffe
3,42971431
asked Nov 11 at 16:09
wizeone
32
edited Nov 11 at 16:20
rsjaffe
3,42971431
edited Nov 11 at 16:20
rsjaffe
3,42971431
edited Nov 11 at 16:20
rsjaffe
3,42971431
3,42971431
asked Nov 11 at 16:09
wizeone
32
asked Nov 11 at 16:09
wizeone
32
asked Nov 11 at 16:09
wizeone
32
32
1
Simplify. Format. Then try again.
– Strawberry
Nov 11 at 16:11
what do you mean "Simplify. Format" as in? (i made this query just to pull the totals to post here, the working code is a lot different)
– wizeone
Nov 11 at 16:18
thank you rsjaffe for fixing the layout i couldn't figure it out when i posted it(was my first post)
– wizeone
Nov 11 at 16:29
add a comment |
1
Simplify. Format. Then try again.
– Strawberry
Nov 11 at 16:11
what do you mean "Simplify. Format" as in? (i made this query just to pull the totals to post here, the working code is a lot different)
– wizeone
Nov 11 at 16:18
thank you rsjaffe for fixing the layout i couldn't figure it out when i posted it(was my first post)
– wizeone
Nov 11 at 16:29
1
1
Simplify. Format. Then try again.
– Strawberry
Nov 11 at 16:11
Simplify. Format. Then try again.
– Strawberry
Nov 11 at 16:11
what do you mean "Simplify. Format" as in? (i made this query just to pull the totals to post here, the working code is a lot different)
– wizeone
Nov 11 at 16:18
what do you mean "Simplify. Format" as in? (i made this query just to pull the totals to post here, the working code is a lot different)
– wizeone
Nov 11 at 16:18
thank you rsjaffe for fixing the layout i couldn't figure it out when i posted it(was my first post)
– wizeone
Nov 11 at 16:29
thank you rsjaffe for fixing the layout i couldn't figure it out when i posted it(was my first post)
– wizeone
Nov 11 at 16:29
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
accepted
Some of your players are playing in / for more than 1 store. Could be one player playing for 4 stores or a different combination like 3 players playing for 2 stores each.
Your query could only work if each player can only be assigned to exactly one store. If that isn't true the result will be inconsistent most of the times.
answered Nov 11 at 16:21
rf1234
39237
that thought totally slipped my mind. thank you for showing me the elephant in the room
– wizeone
Nov 11 at 16:27
is there a simple way of getting the full total in mysql since the total does not account for people going to other stores, or would it be better just to do the simple math in the php script?
– wizeone
Nov 11 at 18:33
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
accepted
Some of your players are playing in / for more than 1 store. Could be one player playing for 4 stores or a different combination like 3 players playing for 2 stores each.
Your query could only work if each player can only be assigned to exactly one store. If that isn't true the result will be inconsistent most of the times.
answered Nov 11 at 16:21
rf1234
39237
that thought totally slipped my mind. thank you for showing me the elephant in the room
– wizeone
Nov 11 at 16:27
is there a simple way of getting the full total in mysql since the total does not account for people going to other stores, or would it be better just to do the simple math in the php script?
– wizeone
Nov 11 at 18:33
add a comment |
up vote
0
down vote
accepted
Some of your players are playing in / for more than 1 store. Could be one player playing for 4 stores or a different combination like 3 players playing for 2 stores each.
Your query could only work if each player can only be assigned to exactly one store. If that isn't true the result will be inconsistent most of the times.
answered Nov 11 at 16:21
rf1234
39237
that thought totally slipped my mind. thank you for showing me the elephant in the room
– wizeone
Nov 11 at 16:27
is there a simple way of getting the full total in mysql since the total does not account for people going to other stores, or would it be better just to do the simple math in the php script?
– wizeone
Nov 11 at 18:33
add a comment |
up vote
0
down vote
accepted
up vote
0
down vote
accepted
Some of your players are playing in / for more than 1 store. Could be one player playing for 4 stores or a different combination like 3 players playing for 2 stores each.
Your query could only work if each player can only be assigned to exactly one store. If that isn't true the result will be inconsistent most of the times.
answered Nov 11 at 16:21
rf1234
39237
Some of your players are playing in / for more than 1 store. Could be one player playing for 4 stores or a different combination like 3 players playing for 2 stores each.
Your query could only work if each player can only be assigned to exactly one store. If that isn't true the result will be inconsistent most of the times.
answered Nov 11 at 16:21
rf1234
39237
edited Nov 11 at 16:26
answered Nov 11 at 16:21
rf1234
39237
answered Nov 11 at 16:21
rf1234
39237
answered Nov 11 at 16:21
rf1234
39237
39237
that thought totally slipped my mind. thank you for showing me the elephant in the room
– wizeone
Nov 11 at 16:27
is there a simple way of getting the full total in mysql since the total does not account for people going to other stores, or would it be better just to do the simple math in the php script?
– wizeone
Nov 11 at 18:33
add a comment |
that thought totally slipped my mind. thank you for showing me the elephant in the room
– wizeone
Nov 11 at 16:27
is there a simple way of getting the full total in mysql since the total does not account for people going to other stores, or would it be better just to do the simple math in the php script?
– wizeone
Nov 11 at 18:33
that thought totally slipped my mind. thank you for showing me the elephant in the room
– wizeone
Nov 11 at 16:27
that thought totally slipped my mind. thank you for showing me the elephant in the room
– wizeone
Nov 11 at 16:27
is there a simple way of getting the full total in mysql since the total does not account for people going to other stores, or would it be better just to do the simple math in the php script?
– wizeone
Nov 11 at 18:33
is there a simple way of getting the full total in mysql since the total does not account for people going to other stores, or would it be better just to do the simple math in the php script?
– wizeone
Nov 11 at 18:33
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53250604%2fconfused-mysql-answers-do-not-add-up-to-same-number%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
1
Simplify. Format. Then try again.
– Strawberry
Nov 11 at 16:11
what do you mean "Simplify. Format" as in? (i made this query just to pull the totals to post here, the working code is a lot different)
– wizeone
Nov 11 at 16:18
thank you rsjaffe for fixing the layout i couldn't figure it out when i posted it(was my first post)
– wizeone
Nov 11 at 16:29