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.