2 count in the same query

Question

I have the following SQL query (written by another person):

select q.id, q.text, v.id, count(v.id) as voteCount
  from survey as s 
  join sessions as ss on ss.session_state='FINISHED' and ss.survey=s.id
  join answer as a on a.sessionId=ss.id 
  join answer_item as a_i on a_i.answer=a.id 
  join question_variant as v on v.id=a_i.question_variant_id
  join question as q on q.id=v.question_id 
  where s.id=9  
  group by q.id, v.id order by q.id, voteCount desc

It outputs stat data from surveys. 9 is just for test. voteCount returns how many times an answer variant was chosen for every question. It works fine:

q.id q.text    v.id voteCount comment for voteCount
10   blahblah  5    2         2 is two times (5 and 5) for question 10
10   blahblah  4    1         1 is one time (4) for question 10
10   blahblah  2    1         1 is one time (2) for question 10
10   blahblah  5    2         2 is two time (5 and 5) for question 10
11   foobarfoo 5    1         1 is one time (5) for question 11

Now I want to modify the query to have another column in the result, which is equal to how many times the question was answered.

q.id q.text    v.id voteCount totalCount comment for totalCount
10   blahblah  5    2         4          4 is four times question 10 is answered
10   blahblah  4    1         4          4 is four times question 10 is answered
10   blahblah  2    1         4          4 is four times question 10 is answered
10   blahblah  5    2         4          4 is four times question 10 is answered
11   foobarfoo 5    1         1          1 is one time question 11 is answered

I've tried this:

select q.id, q.text, v.id, count(v.id) as voteCount, count(q.id) as totalCount
  from survey as s 
  join sessions as ss on ss.session_state='FINISHED' and ss.survey=s.id
  join answer as a on a.sessionId=ss.id 
  join answer_item as a_i on a_i.answer=a.id 
  join question_variant as v on v.id=a_i.question_variant_id
  join question as q on q.id=v.question_id 
  where s.id=9  
  group by q.id, v.id order by q.id, voteCount desc

But surprisingly both columns contain the same value!

q.id q.text    v.id voteCount totalCount comment for 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 and how to fix this?

Answer 1

That's because you are basically just counting rows in the group, and the group is the same for each of those ids. If you would just want the totals, you might use DISTINCT to count distinct IDs:

select 
  count(distinct v.id) as voteCount, 
  count(distinct q.id) as totalCount
....

But you are also grouping by q.id and v.id to get individual properties of the question (like the title), so COUNT DISTINCT won't do you any good. The result will always be 1 for each of them. It seems you want to use analytical functions (window functions), which exist in Oracle and various other databases, but not in MySQL.

There are tricks to simulate those functions (See DBA.SE - MySQL and window functions), but in this case, I think two subselects will do the trick for you:

select 
  q.id, 
  q.text, 
  v.id, 
  (select
    count(vx.id)
  from
    question_variant vx
  where
    vx.id = v.id) as VoteCount,
  (select
    count(qx.id)
  from
    question qx
  where
    qx.id = q.id) as TotalCount
from 
  survey as s 
  join sessions as ss on ss.session_state='FINISHED' and ss.survey=s.id
  join answer as a on a.sessionId=ss.id 
  join answer_item as a_i on a_i.answer=a.id 
  join question_variant as v on v.id=a_i.question_variant_id
  join question as q on q.id=v.question_id 
where 
  s.id=9  
group by 
  q.id, v.id 
order by 
  q.id, 
  voteCount desc

Instead of group by, you might as well use select distinct in this query. Main point is, that you select all the fields you like, and for each row, you separately count the number of times the question of that row was answered and the number of votes cast on the question_variant of that row.

Answer 2

COUNT() and other aggregate functions are applied on a per-group basis. Since q.id and v.id are grouping columns, their counts for each group will each be the number results in that group (or possibly zero in a few groups, if those columns are nullable).

I'm not certain that I understand what other information you're trying to extract, but it sounds like you want to group your results differently, in which case you probably need a separate query. I can only guess what that might be based on your question and your other query, but maybe it would be something like this:

select q.id, count(*) as answerCount
from survey as s 
  join sessions as ss on ss.session_state='FINISHED' and ss.survey=s.id
  join answer as a on a.sessionId=ss.id 
  join answer_item as a_i on a_i.answer=a.id 
  join question_variant as v on v.id=a_i.question_variant_id
  join question as q on q.id=v.question_id 
where s.id=9  
group by q.id