join table and count them SQL based on segment 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.
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.