On 07/02/2014 09:55 AM, Arup Rakshit wrote:
SELECT users.gender,count(*) as participant,avg(daily_action_answers.value) as
value
FROM "users" INNER JOIN "daily_action_answers" ON
"daily_action_answers"."user_id" = "users"."id"
INNER JOIN "measures" ON "measures"."id" = "daily_action_answers"."measure_id"
WHERE (((daily_action_answers.day between now() and <last_date_of_year>) and
daily_action_answers.daily_action_id = 1))
GROUP BY users.gender, measures.option
This is producing the below
gender | participants | value
n 2 12
n 1 3
m 1 4
m 4 12
f 3 23
f 4 15
Here n.m,f it comes 2 times, because the possible answer is 2. That's the
problem with my current query. I don't understand which average value for
which answer.
Can we make the output as below ?
gender participants answer1_avg answer2_avg
n 3 12 3
m 5 4 12
f 7 15 23
As mentioned by jared, the problem is the additional group by
measures.option which needs to be eliminated. To better understand what
is happening, just add measures.option to your list of output columns.
Right now the grouping is hidden because you aren't showing that column.
Cheers,
Steve
