Re: Not able to understand how to write group by

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