Flatten a response to an SQL query

user_4599824 0
Viewed 86

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.

mysql sql
2 Answers

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.

user_51760710

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
user_17613070
Related