How to count Active records with condition ? - Advanced SQL query

Let's take an example. If you would like to fetch list of students with how many books they purchased, and suppose your transaction table contains book status as "Requested" and "Purchased".

Following query will help you to achieve this :

@students = Student.joins(:student_books).where("ANY CONDITION").select("*, count(CASE WHEN student_books.status = 'Purchased' THEN 1 END ) as purchased_book_count").group("student_books.student_id")

You can access this  purchased_book_count as following:

student = @students.first

purchased_book_count = student.purchased_book_count

Happy Coding ...!!