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

Steve Crawford wrote
> On 07/02/2014 09:55 AM, Arup Rakshit wrote:
>> 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
>>
>>
>>
> As mentioned by jared, the problem is the additional group by
> measures.option which needs to be eliminated. To better understand what
> is happening, just add measures.option to your list of output columns.
> Right now the grouping is hidden because you aren't showing that column.

Are you sure this is what you want?

Since there are two columns you will have to either use a CASE or a select
to facilitate calculating the values for each of the columns.

SELECT gender, answer1_avg, answer2_avg
FROM (SELECT DISTINCT gender FROM ...) gn
LEFT JOIN (SELECT gender, answer1_avg FROM ...) ans1 USING (gender)
LEFT JOIN (SELECT gender, answer2_avg FROM ...) ans2 USING (gender)

You could also try learning "crosstab" from the "tablefunc" extension:

http://www.postgresql.org/docs/9.3/interactive/tablefunc.html

I do not see how a single "participant count" column will provide a
meaningful piece of data...

David J.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Not-able-to-understand-how-to-write-group-by-tp5810250p5810283.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.