Ndtyjky

I am trying to get a flat table for a question and answer table with mysql. This are my tables.

PollQuestion

PollID | Question| A | B | C | D | E | TimeStamp

PollReponse

PollReponseID | PollID | UserID | Answer

In the PollAnswer table I get Five answers as VARCHAR A, B, C, D, E.

I have written a query to group the answers by A, B, C, D, E.

    select q.Question

     , q.PollID

     , r.Answer

     , count(r.Answer) 

  from pollQuestions q

     , pollResponse r

 where  q.PollID = r.PollID 

 group 

    by r.Answer

     , q.Question

     , q.PollID 

 order 

    by r.PollID;

Which gives me a response as follows.

Question | PollID | Answer | count

alpha    |  1     | A     | 2 

alpha    |  1     | B     | 3 

alpha    |  1     | C     | 4 

alpha    |  1     | D     | 0

alpha    |  1     | E     | 0 

betas    |  2     | A     | 3 

betas    |  2     | B     | 4 

betas    |  2     | C     | 4 

betas    |  2     | D     | 6

betas    |  2     | E     | 0 

I want to flatten the answer like this.

Question | PollID | countA | countB | countC | countD | countE

alpha    |  1     | 2      | 2      |  4     |  0     |   0 

betas    |  2     | 3      | 4      |  4     |  6     |   0 

Is there anyway I can achieve this without changing the table structure?

Any pointer would be appreciated.

You can try to use condition aggregate function.

select 

    pollQuestions.Question, 

    pollQuestions.PollID, 

    count(CASE WHEN pollResponse.Answer ='A' THEN 1 END) countA,

    count(CASE WHEN pollResponse.Answer ='B' THEN 1 END) countB,

    count(CASE WHEN pollResponse.Answer ='C' THEN 1 END) countC,

    count(CASE WHEN pollResponse.Answer ='D' THEN 1 END) countD,

    count(CASE WHEN pollResponse.Answer ='E' THEN 1 END) countE

from pollQuestions 

JOIN pollResponse on pollQuestions.PollID = pollResponse.PollID  

group by 

    pollQuestions.Question,

    pollQuestions.PollID  

order by  

    pollResponse.PollID;

NOTE

I would use JOIN instead of comma , because JOIN syntax is clearer than , about connect two tabls.

You can use group by with sum(case when...) like below:

select pq.Question, pq.PollID,

       sum(case when pr.Answer = 'A' then 1 else 0 end) as 'countA',

       sum(case when pr.Answer = 'B' then 1 else 0 end) as 'countB',

       sum(case when pr.Answer = 'C' then 1 else 0 end) as 'countC',

       sum(case when pr.Answer = 'D' then 1 else 0 end) as 'countD',

       sum(case when pr.Answer = 'E' then 1 else 0 end) as 'countE'

from PollQuestion pq

left join PollResponse pr on pq.PollID = pr.PollID

group by pq.Question, pq.PollID