Re: Not able to understand how to write group by

On 02 Jul 2014, at 18:55, 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

> 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.