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.
Best Answer
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.