On 02 Jul 2014, at 18:55, Arup Rakshit <aruprakshit@xxxxxxxxxxxxxx> 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 > 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 Following the discussion, if this is really only about a fixed number of measures you can solve that by using the CASE statement for each measure involved and the fact that aggregate functions skip NULL-values, like so: SELECT users.gender,count(*) as participant, avg(CASE WHEN measures.id = 1 THEN daily_action_answers.value ELSE NULL END) as value1, avg(CASE WHEN measures.id = 2 THEN daily_action_answers.value ELSE NULL END) as value2 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 BTW, I noticed you are mixing how you quote the same identifiers. Quoting identifiers makes them case-sensitive, so either always quote them or never quote them, but don’t mix or you’ll get into trouble if you ever end up in a database(-version) where identifiers are case-folded to upper case (which is pretty much any database different from PG). Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
