I have a relationship between Job Shifts and People who have indicated whether they want to apply of the jobs (Yes, No or Maybe).

I Want to list a Job Shift and the Count of people that have indicated Yes, No or Maybe.

My Current Query Does not work and I not sure why, I am new to Rails so I would also like to know if there is a more Railish way of doing this.

![enter image description here](https://i.stack.imgur.com/oAGWq.png)

```
def report_shift_detail
    @headers = ["id", "staffroom_id", "title", "Yes", "No", "Maybe"]
    @fields = [:id, :staffroom_id, :title, :count_yes, :count_no, :count_maybe]

    @sql = Shift.scoped
      .select("shifts.id, shifts.staffroom_id, shifts.title, count(application_yes.id) as count_yes, count(application_no.id) as count_no, count(application_maybe.id) as count_maybe")
      .joins("INNER JOIN shift_applications as application_yes ON application_yes.shift_id = shifts.id")
      .where("application_yes.sort = 'yes'")
      .joins("INNER JOIN shift_applications as application_no ON application_no.shift_id = shifts.id")
      .where("application_no.sort = 'no'")
      .joins("INNER JOIN shift_applications as application_maybe ON application_maybe.shift_id = shifts.id")
      .where("application_maybe.sort = 'maybe'")
      .group("shifts.id")
end

```