I have 2 tables and I'm trying to join and count how many people have answer each questions. I'm trying to find a better way to have only two column. the first one will be of people who answer "Just Me,My Spouse/Partner" and the other column who people who answer Children 0-5yr, 6-12yr,13-17yr. I'm trying to figure out a better way to have a table that looks like this

Question                  Answer               Children     Justme/partner
do you buy milk           weekly                400            20
do you consume cheese     daily                 15             300
how many time you drive   daily                 400            220  

here are the 2 tables This is the table called "household" that is provided:

User    JustMe      Children 0-5      Children 6-12    Myspouse/partner
  5      N              N               N                  N                           
  5      N              N               N                  N
  4      N              N               N                  N
  4      N              N               N                  N
 12      N              N               N                  N
 12     Just Me         N               N              Myspousepartner           
 46     Just Me         N        Children 6-12 years   Mypousepartner
 46     Just Me         N        Children 6-12 years   Mypousepartner

Survey table

 User       questionid   answerid   question                   answer
  4          2             5         how often you buy gas     weekly
  46         3             4         how often you buy milk    monthly
  75         3             4         how often you buy milk     weekly
  13         4             2         how often you use coupon   monthly

I used the

  SELECT id,question, Children_0_5_years, Children_6_12_years,  
  Children_13_17_years, count(*) AS NEW_COUNT
  FROM table1 inner join table2 on table1.user=table2.user group by...

unfortunately this query return things that I didn't need.

1 Answer 1


SELECT Question, SUM( CASE --Case for each column WHEN h."Children 0-5" = 'Children 0-5' THEN 1 Else 0 END) as "Children 0-5", SUM( CASE
WHEN h."Children 6-12" = 'Children 0-5' THEN 1 Else 0 END as "Children 0-5"* from household h INNER JOIN survey s ON h.user = s.user GROUP BY s.question, answer

Ok doing this from my brain rather than testing but here is the idea. Use case statements to turn your string data into a sum-able number. Then group by the question to aggregate the answers.

If I were you (and you could change the data) I would get away from verbose string fields and make things like the age groups Y, N or even better 1, 0.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.