From: | Arup Rakshit <aruprakshit(at)rocketmail(dot)com> |
---|---|
To: | David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Not able to understand how to write group by |
Date: | 2014-07-03 07:13:18 |
Message-ID: | 1404371598.52315.YahooMailNeo@web193903.mail.sg3.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Are you sure this is what you want?
Since there are two columns you will have to either use a CASE or a
<sub-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...
Finally I wrote
SELECT users.gender,count(*) as participant,
case when daily_action_answers.measure_id = 1 then avg(daily_action_answers.value) end as cpd,
case when daily_action_answers.measure_id = 2 then avg(daily_action_answers.value) end as other
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 '2014-07-03' and '2014-12-31')and daily_action_answers.daily_action_id = 1))
GROUP BY users.gender, daily_action_answers.measure_id
gender | participant | cpd |other
"Female", 2 , 8.5, 0.0
"Female", 1 , 0.0, 8.0
"None", 2, 6.5, 0.0
"None", 1, 0.0, 5.0
"Male", 1, 4.0, 0.0,
"Male", 2, 0.0, 10.0
Problem is.. I am not able to merge those pair rows into one... :-(
From | Date | Subject | |
---|---|---|---|
Next Message | Dave Page | 2014-07-03 07:54:32 | Re: Windows releases - Bundle OpenSSL includes and .libs in the installer? |
Previous Message | Alban Hertroys | 2014-07-03 06:52:10 | Re: Not able to understand how to write group by |