you have: GROUP BY users.gender, measures.option instead try: GROUP BY users
On Wed, Jul 2, 2014 at 12:55 PM, Arup Rakshit <arupraks...@rocketmail.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
