Re: Not able to understand how to write group by

you have:
GROUP BY users.gender, measures.option

instead try:
GROUP BY users

On Wed, Jul 2, 2014 at 12:55 PM, Arup Rakshit <aruprakshit(at)rocketmail(dot)com>
wrote:

> Hi,
>
> I am working on web development project. There I am using this awesome DB.
> Let
> me tell you first the schema that I am having associated the problem.
>
> I am having a table *users* - which has many fields. Out of them, the one I
> need here is *gender*. This column can have value "f"/"m"/"n".
>
> I have a table called *measures*. This table contains all possible answers
> of
> questions lies in the table called *daily_actions*. It has a foreign key
> columns as *daily_action_id*.
>
> I have a table called *daily_actions*. It has a field *question* and
> several
> other fields too.
>
> I have a table called *daily_action_answers*. It has foreign keys called
> "user_id", "daily_action_id" and "measure_id". Another field is *value* and
> "day". *day* is a _date_ field.
>
>
>
> 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
>
>
> Please let me know if you need any more information on this ?
>
> ================
> Regards,
> Arup Rakshit
> ================
> Debugging is twice as hard as writing the code in the first place.
> Therefore,
> if you write the code as cleverly as possible, you are, by definition, not
> smart enough to debug it.
>
> --Brian Kernighan
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>