Search Postgresql Archives

Re: Not able to understand how to write group by

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 







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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux