MYSQL getting poll results tableHow to output MySQL query results in CSV format?Should I use the datetime or timestamp data type in MySQL?Rewrite Subquery(not in) as JoinHow to get a list of MySQL user accountsWant to learn to improve slow mysql querymysql join: what is faster?MySQL does joining on MUL keys impact performance?MySQL tricky triple joinHow can I get the attributes from a mysql table by specific column?desc table in mysql say Null is No but default is NULL?
The IT department bottlenecks progress. How should I handle this?
What does chmod -u do?
Multiplicative persistence
Is it safe to use olive oil to clean the ear wax?
How do I color the graph in datavisualization?
What is Cash Advance APR?
Offered money to buy a house, seller is asking for more to cover gap between their listing and mortgage owed
Are the IPv6 address space and IPv4 address space completely disjoint?
Electoral considerations aside, what are potential benefits, for the US, of policy changes proposed by the tweet recognizing Golan annexation?
How to explain what's wrong with this application of the chain rule?
A social experiment. What is the worst that can happen?
What should you do when eye contact makes your subordinate uncomfortable?
How much character growth crosses the line into breaking the character
WiFi Thermostat, No C Terminal on Furnace
Removing files under particular conditions (number of files, file age)
"Spoil" vs "Ruin"
Where does the bonus feat in the cleric starting package come from?
The screen of my macbook suddenly broken down how can I do to recover
What are the purposes of autoencoders?
How to implement a feedback to keep the DC gain at zero for this conceptual passive filter?
Has any country ever had 2 former presidents in jail simultaneously?
Should I stop contributing to retirement accounts?
Pre-mixing cryogenic fuels and using only one fuel tank
Why does the Sun have different day lengths, but not the gas giants?
MYSQL getting poll results table
How to output MySQL query results in CSV format?Should I use the datetime or timestamp data type in MySQL?Rewrite Subquery(not in) as JoinHow to get a list of MySQL user accountsWant to learn to improve slow mysql querymysql join: what is faster?MySQL does joining on MUL keys impact performance?MySQL tricky triple joinHow can I get the attributes from a mysql table by specific column?desc table in mysql say Null is No but default is NULL?
I have 2 tables, one with poll answers and one with votes:
DESC polls;
Field Type Null Key Default Extra
id int(11) NO PRI NULL auto_increment
pollId int(11) NO MUL NULL
answer varchar(150) YES NULL
DESC votes;
Field Type Null Key Default Extra
id int(11) NO PRI NULL auto_increment
pollId int(11) NO MUL NULL
answerId int(11) NO MUL NULL
userId int(11) NO MUL NULL
I am trying to get following results with all answers and votes:
pollId answerId numberOfVotes
1 1 20
1 2 10
1 3 0
I tried right joins of votes to answers, but it does not work:
SELECT answers.id, COUNT(votes.answerId) FROM answers JOIN votes ON votes.pollId = answers.pollId GROUP BY votes.pollId;
mysql
asked Mar 8 at 4:43
Nodir NasirovNodir Nasirov
384113
add a comment |
I have 2 tables, one with poll answers and one with votes:
DESC polls;
Field Type Null Key Default Extra
id int(11) NO PRI NULL auto_increment
pollId int(11) NO MUL NULL
answer varchar(150) YES NULL
DESC votes;
Field Type Null Key Default Extra
id int(11) NO PRI NULL auto_increment
pollId int(11) NO MUL NULL
answerId int(11) NO MUL NULL
userId int(11) NO MUL NULL
I am trying to get following results with all answers and votes:
pollId answerId numberOfVotes
1 1 20
1 2 10
1 3 0
I tried right joins of votes to answers, but it does not work:
SELECT answers.id, COUNT(votes.answerId) FROM answers JOIN votes ON votes.pollId = answers.pollId GROUP BY votes.pollId;
mysql
asked Mar 8 at 4:43
Nodir NasirovNodir Nasirov
384113
add a comment |
I have 2 tables, one with poll answers and one with votes:
DESC polls;
Field Type Null Key Default Extra
id int(11) NO PRI NULL auto_increment
pollId int(11) NO MUL NULL
answer varchar(150) YES NULL
DESC votes;
Field Type Null Key Default Extra
id int(11) NO PRI NULL auto_increment
pollId int(11) NO MUL NULL
answerId int(11) NO MUL NULL
userId int(11) NO MUL NULL
I am trying to get following results with all answers and votes:
pollId answerId numberOfVotes
1 1 20
1 2 10
1 3 0
I tried right joins of votes to answers, but it does not work:
SELECT answers.id, COUNT(votes.answerId) FROM answers JOIN votes ON votes.pollId = answers.pollId GROUP BY votes.pollId;
mysql
asked Mar 8 at 4:43
Nodir NasirovNodir Nasirov
384113
I have 2 tables, one with poll answers and one with votes:
DESC polls;
Field Type Null Key Default Extra
id int(11) NO PRI NULL auto_increment
pollId int(11) NO MUL NULL
answer varchar(150) YES NULL
DESC votes;
Field Type Null Key Default Extra
id int(11) NO PRI NULL auto_increment
pollId int(11) NO MUL NULL
answerId int(11) NO MUL NULL
userId int(11) NO MUL NULL
I am trying to get following results with all answers and votes:
pollId answerId numberOfVotes
1 1 20
1 2 10
1 3 0
I tried right joins of votes to answers, but it does not work:
SELECT answers.id, COUNT(votes.answerId) FROM answers JOIN votes ON votes.pollId = answers.pollId GROUP BY votes.pollId;
mysql
mysql
asked Mar 8 at 4:43
Nodir NasirovNodir Nasirov
384113
asked Mar 8 at 4:43
Nodir NasirovNodir Nasirov
384113
asked Mar 8 at 4:43
Nodir NasirovNodir Nasirov
384113
asked Mar 8 at 4:43
Nodir NasirovNodir Nasirov
384113
asked Mar 8 at 4:43
Nodir NasirovNodir Nasirov
384113
384113
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
use left join
DEMO
SELECT polls.pollId,polls.id,COUNT(userid) as counts
FROM polls left JOIN votes ON votes.pollId = polls.pollId and
polls.id=votes.answerId
GROUP BY polls.pollId,polls.id order by polls.id
OUTPUT:
pollId id counts
14 17 0
14 18 2
14 19 0
14 20 0
answered Mar 8 at 4:45
fa06fa06
17.5k21018
I tried this, it gives me incorrect result where it says that answers with 0 votes have votes.
– Nodir Nasirov
Mar 8 at 4:47
can u add some sample data then it'll be easy to get it @NodirNasirov
– fa06
Mar 8 at 4:48
select * from answers; id pollId answer 17 14 black 18 14 brown 19 14 white 20 14 bold select * from votes; id pollId answerId userId 5 14 18 3 6 14 18 2
– Nodir Nasirov
Mar 8 at 4:53
@NodirNasirov, and from this rows what is your expected output?
– fa06
Mar 8 at 4:56
answerId countVotes 17 0 18 2 19 0 20 0
– Nodir Nasirov
Mar 8 at 4:58
|
show 1 more 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%2f55056855%2fmysql-getting-poll-results-table%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
use left join
DEMO
SELECT polls.pollId,polls.id,COUNT(userid) as counts
FROM polls left JOIN votes ON votes.pollId = polls.pollId and
polls.id=votes.answerId
GROUP BY polls.pollId,polls.id order by polls.id
OUTPUT:
pollId id counts
14 17 0
14 18 2
14 19 0
14 20 0
answered Mar 8 at 4:45
fa06fa06
17.5k21018
I tried this, it gives me incorrect result where it says that answers with 0 votes have votes.
– Nodir Nasirov
Mar 8 at 4:47
can u add some sample data then it'll be easy to get it @NodirNasirov
– fa06
Mar 8 at 4:48
select * from answers; id pollId answer 17 14 black 18 14 brown 19 14 white 20 14 bold select * from votes; id pollId answerId userId 5 14 18 3 6 14 18 2
– Nodir Nasirov
Mar 8 at 4:53
@NodirNasirov, and from this rows what is your expected output?
– fa06
Mar 8 at 4:56
answerId countVotes 17 0 18 2 19 0 20 0
– Nodir Nasirov
Mar 8 at 4:58
|
show 1 more comment
use left join
DEMO
SELECT polls.pollId,polls.id,COUNT(userid) as counts
FROM polls left JOIN votes ON votes.pollId = polls.pollId and
polls.id=votes.answerId
GROUP BY polls.pollId,polls.id order by polls.id
OUTPUT:
pollId id counts
14 17 0
14 18 2
14 19 0
14 20 0
answered Mar 8 at 4:45
fa06fa06
17.5k21018
I tried this, it gives me incorrect result where it says that answers with 0 votes have votes.
– Nodir Nasirov
Mar 8 at 4:47
can u add some sample data then it'll be easy to get it @NodirNasirov
– fa06
Mar 8 at 4:48
select * from answers; id pollId answer 17 14 black 18 14 brown 19 14 white 20 14 bold select * from votes; id pollId answerId userId 5 14 18 3 6 14 18 2
– Nodir Nasirov
Mar 8 at 4:53
@NodirNasirov, and from this rows what is your expected output?
– fa06
Mar 8 at 4:56
answerId countVotes 17 0 18 2 19 0 20 0
– Nodir Nasirov
Mar 8 at 4:58
|
show 1 more comment
use left join
DEMO
SELECT polls.pollId,polls.id,COUNT(userid) as counts
FROM polls left JOIN votes ON votes.pollId = polls.pollId and
polls.id=votes.answerId
GROUP BY polls.pollId,polls.id order by polls.id
OUTPUT:
pollId id counts
14 17 0
14 18 2
14 19 0
14 20 0
answered Mar 8 at 4:45
fa06fa06
17.5k21018
use left join
DEMO
SELECT polls.pollId,polls.id,COUNT(userid) as counts
FROM polls left JOIN votes ON votes.pollId = polls.pollId and
polls.id=votes.answerId
GROUP BY polls.pollId,polls.id order by polls.id
OUTPUT:
pollId id counts
14 17 0
14 18 2
14 19 0
14 20 0
answered Mar 8 at 4:45
fa06fa06
17.5k21018
edited Mar 8 at 5:05
answered Mar 8 at 4:45
fa06fa06
17.5k21018
answered Mar 8 at 4:45
fa06fa06
17.5k21018
answered Mar 8 at 4:45
fa06fa06
17.5k21018
17.5k21018
I tried this, it gives me incorrect result where it says that answers with 0 votes have votes.
– Nodir Nasirov
Mar 8 at 4:47
can u add some sample data then it'll be easy to get it @NodirNasirov
– fa06
Mar 8 at 4:48
select * from answers; id pollId answer 17 14 black 18 14 brown 19 14 white 20 14 bold select * from votes; id pollId answerId userId 5 14 18 3 6 14 18 2
– Nodir Nasirov
Mar 8 at 4:53
@NodirNasirov, and from this rows what is your expected output?
– fa06
Mar 8 at 4:56
answerId countVotes 17 0 18 2 19 0 20 0
– Nodir Nasirov
Mar 8 at 4:58
|
show 1 more comment
I tried this, it gives me incorrect result where it says that answers with 0 votes have votes.
– Nodir Nasirov
Mar 8 at 4:47
can u add some sample data then it'll be easy to get it @NodirNasirov
– fa06
Mar 8 at 4:48
select * from answers; id pollId answer 17 14 black 18 14 brown 19 14 white 20 14 bold select * from votes; id pollId answerId userId 5 14 18 3 6 14 18 2
– Nodir Nasirov
Mar 8 at 4:53
@NodirNasirov, and from this rows what is your expected output?
– fa06
Mar 8 at 4:56
answerId countVotes 17 0 18 2 19 0 20 0
– Nodir Nasirov
Mar 8 at 4:58
I tried this, it gives me incorrect result where it says that answers with 0 votes have votes.
– Nodir Nasirov
Mar 8 at 4:47
I tried this, it gives me incorrect result where it says that answers with 0 votes have votes.
– Nodir Nasirov
Mar 8 at 4:47
can u add some sample data then it'll be easy to get it @NodirNasirov
– fa06
Mar 8 at 4:48
can u add some sample data then it'll be easy to get it @NodirNasirov
– fa06
Mar 8 at 4:48
select * from answers; id pollId answer 17 14 black 18 14 brown 19 14 white 20 14 bold select * from votes; id pollId answerId userId 5 14 18 3 6 14 18 2– Nodir Nasirov
Mar 8 at 4:53
select * from answers; id pollId answer 17 14 black 18 14 brown 19 14 white 20 14 bold select * from votes; id pollId answerId userId 5 14 18 3 6 14 18 2– Nodir Nasirov
Mar 8 at 4:53
@NodirNasirov, and from this rows what is your expected output?
– fa06
Mar 8 at 4:56
@NodirNasirov, and from this rows what is your expected output?
– fa06
Mar 8 at 4:56
answerId countVotes 17 0 18 2 19 0 20 0– Nodir Nasirov
Mar 8 at 4:58
answerId countVotes 17 0 18 2 19 0 20 0– Nodir Nasirov
Mar 8 at 4:58
|
show 1 more 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%2f55056855%2fmysql-getting-poll-results-table%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
