Re: Not able to understand how to write group by - Mailing list pgsql-general

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