join table and count them SQL based on segment

Question

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.

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.