Ruby 按总和特定值联接查询组

Question

@rentschedule_cc = 
    RentSchedule.joins([{:rent => {:lease => :lease_rent_rolls}}, :charge_code])
                .where("lease_rent_rolls.id = 453861 and
                        rent_schedules.from_date < '#{Date.today.strftime("%Y-%m-%d %H:%M:%s")}' and 
                        rent_schedules.to_date > '#{Date.today.strftime("%Y-%m-%d %H:%M:%s")}' and 
                        rent_schedules.charge_code_id IS NOT NULL")
                .group("charge_codes.charge_type")

我正在获得分组依据中的第一条记录，但我想要sum(rent_schedules.annual_psf) 和sum(rent_schedules.monthly_amt).如何获取此查询中的总和值？

Answer 1

您可以在选择

@rentschedule_cc = 
    RentSchedule.joins([{:rent => {:lease => :lease_rent_rolls}}, :charge_code])
            .where("lease_rent_rolls.id = 453861 and
                    rent_schedules.from_date < '#{Date.today.strftime("%Y-%m-%d %H:%M:%s")}' and 
                    rent_schedules.to_date > '#{Date.today.strftime("%Y-%m-%d %H:%M:%s")}' and 
                    rent_schedules.charge_code_id IS NOT NULL")
            .group("charge_codes.charge_type")
            .select("rent_schedules.*, sum(rent_schedules.annual_psf) as sum1, sum(rent_schedules.monthly_amt) as sum2")

并获取结果中的sum1和sum2值