Request a Demo
Home   >   mailing lists

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

From Arup Rakshit
Subject Re: Not able to understand how to write group by
Date
Msg-id 1404385295.4791.YahooMailNeo@web193903.mail.sg3.yahoo.com
Whole thread Raw
In response to Re: Not able to understand how to write group by  (David G Johnston <david.g.johnston@gmail.com>)
Responses Re: Not able to understand how to write group by
List pgsql-general
Tree view



Without commenting on the rest of it...to combine what you show here just
GROUP BY gender and SUM() everything else (i.e., turn the above into a
subquery and then do this)

David J.


Exactly.. I am done. Here is the ORM query :-

  def self.employee_learning_by_gender(question_id)
    cpd_id = Measure.find_by(option: 'CPD').id
    other_id = Measure.find_by(option: 'Others').id
   
    User.select("view.gender, sum(view.participant) as participant, sum(cpd) as cpd, sum(other) as other").from(User.joins(daily_action_answers: [:measure]).group("users.gender, daily_action_answers.measure_id")
                                                .where("((daily_action_answers.day between ? and ?) and
                                                         daily_action_answers.daily_action_id = ?)",
                                                         Date.today, Date.today.end_of_year,
                                                         question_id
                                                       )
                                                .select("users.gender, count(*) as participant,
                                                         case when daily_action_answers.measure_id = #{cpd_id} then avg(daily_action_answers.value) end as cpd,
                                                         case when daily_action_answers.measure_id = #{other_id} then avg(daily_action_answers.value) end as other"
                                                       ), :view).group("view.gender")
  end

pgsql-general by date:

Previous
From: Orestis Tsakiridis
Date:
Subject: postgresql server version & JDBC driver version
Next
From: Augori
Date:
Subject: Failed to build transaction for opengeo-server