Group By : As we all know that group by in any RDBMS provides us with a way to get aggregate of rows based on any particular column. Taking it in point of rails the enumerable class in Rails contains a method named 'group_by'. This method is a pure magic for a developer below is an example regarding students which will help us understand it's power :

Student.group_by(&:week).each do |week, students|

By specifying &:week we tell group_by that we want to group by the result of the week attribute of every student. This is the attribute we specified earlier in the model.Well, when the grouping is done we create a block that will handle every group of items. We extract 'week' and 'posts' here 'week' contains the week number and 'students' all the students for that week.

group_by does not sort the results on it's own so as to sort the results we have to do the following :

Student.group_by(&:week).sort.each do |week, students|

Where or Having with group by : We are often confussed what to use with a query having group by clause where or having . Well both where or having have their specific use.

The main reason for using WHERE clause is to select rows that are to be included in the query. Suppose we want all the students who have attended class on friday :

Student.find(:all, :conditions => { :week => 2 })

Suppose I want the total classes attended by a student in a particular week. In that case, I would need to use the GROUP BY clause to build an aggregate query.

Student.find(:all, :group => params[:week], :conditions => { :week => 2 })

Suppose I want the same information, but I don't care about student who have not attended a single class. Since the total class attended by a student is an aggregate figure, i.e. the figure is generated from a group of records, you must use HAVING to select the proper data.

Student.where(:week => 2).group(:week).having("total > 2")

So below is the rules which must be taken care of while using where or having :

1) You can't use HAVING unless you also use GROUP BY

2) If a condition refers to an aggregate function, put that condition in the HAVING clause. Otherwise, use the WHERE clause.