mysql - 2 count in the same query -

mysql - 2 count in the same query -

i have next sql query (written person):

select q.id, q.text, v.id, count(v.id) votecount survey s bring together sessions ss on ss.session_state='finished' , ss.survey=s.id bring together reply on a.sessionid=ss.id bring together answer_item a_i on a_i.answer=a.id bring together question_variant v on v.id=a_i.question_variant_id bring together question q on q.id=v.question_id s.id=9 grouping q.id, v.id order q.id, votecount desc

it outputs stat info surveys. 9 test. votecount returns how many times reply variant chosen every question. works fine:

q.id q.text v.id votecount comment votecount 10 blahblah 5 2 2 2 times (5 , 5) question 10 10 blahblah 4 1 1 1 time (4) question 10 10 blahblah 2 1 1 1 time (2) question 10 10 blahblah 5 2 2 2 time (5 , 5) question 10 11 foobarfoo 5 1 1 1 time (5) question 11

now want modify query have column in result, equal how many times question answered.

q.id q.text v.id votecount totalcount comment totalcount 10 blahblah 5 2 4 4 4 times question 10 answered 10 blahblah 4 1 4 4 4 times question 10 answered 10 blahblah 2 1 4 4 4 times question 10 answered 10 blahblah 5 2 4 4 4 times question 10 answered 11 foobarfoo 5 1 1 1 1 time question 11 answered

i've tried this:

select q.id, q.text, v.id, count(v.id) votecount, count(q.id) totalcount survey s bring together sessions ss on ss.session_state='finished' , ss.survey=s.id bring together reply on a.sessionid=ss.id bring together answer_item a_i on a_i.answer=a.id bring together question_variant v on v.id=a_i.question_variant_id bring together question q on q.id=v.question_id s.id=9 grouping q.id, v.id order q.id, votecount desc

but surprisingly both columns contain same value!

q.id q.text v.id votecount totalcount comment totalcount 10 blahblah 5 2 2 ??? 10 blahblah 4 1 1 ??? 10 blahblah 2 1 1 ??? 10 blahblah 5 2 2 ??? 11 foobarfoo 5 1 1 ???

why , how prepare this?

count() , other aggregate functions applied on per-group basis. since q.id , v.id grouping columns, counts each grouping each number results in grouping (or perchance 0 in few groups, if columns nullable).

i'm not understand other info you're trying extract, sounds want grouping results differently, in case need separate query. can guess might based on question , other query, maybe this:

select q.id, count(*) answercount survey s bring together sessions ss on ss.session_state='finished' , ss.survey=s.id bring together reply on a.sessionid=ss.id bring together answer_item a_i on a_i.answer=a.id bring together question_variant v on v.id=a_i.question_variant_id bring together question q on q.id=v.question_id s.id=9 grouping q.id

mysql count