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.

edited Nov 11 at 16:20

rsjaffe

3,42971431

asked Nov 11 at 16:09

wizeone

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

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.

edited Nov 11 at 16:20

rsjaffe

3,42971431

asked Nov 11 at 16:09

wizeone

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

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.

edited Nov 11 at 16:20

rsjaffe

3,42971431

asked Nov 11 at 16:09

wizeone

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

edited Nov 11 at 16:20

rsjaffe

3,42971431

asked Nov 11 at 16:09

wizeone

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

asked Nov 11 at 16:09

wizeone

asked Nov 11 at 16:09

wizeone

asked Nov 11 at 16:09

wizeone

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

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 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.

edited Nov 11 at 16:26

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 |

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',
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
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

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

Name

Required, but never shown

1 Answer
1

active

oldest

votes

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.

edited Nov 11 at 16:26

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.

edited Nov 11 at 16:26

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.

edited Nov 11 at 16:26

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.

edited Nov 11 at 16:26

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

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

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 |

draft saved

draft discarded

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.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Nrthugu