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...
David J.
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... :-(
