On 02 Jul 2014, at 18:55, Arup Rakshit <[email protected]> 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.
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
