Group and having in Ruby on Rails

Introduction

Grouping helps you organize data by common values. Having lets you filter these groups based on conditions.

When you want to count how many records share the same attribute.

When you want to find groups with more than a certain number of items.

When you want to summarize data by categories and filter those summaries.

When you want to find average or total values per group and filter by those values.

Syntax

Ruby on Rails

Model.group(:column_name).having('condition').select('columns')

group groups records by the given column.

having filters groups after grouping, similar to WHERE but for groups.

Examples

Counts users in each city.

Ruby on Rails

User.group(:city).count

Finds order statuses with more than 5 orders.

Ruby on Rails

Order.group(:status).having('count(*) > 5').count

Finds categories where average price is over 100.

Ruby on Rails

Product.group(:category).having('avg(price) > 100').average(:price)

Sample Program

This code groups users by their city, then filters to only show cities with more than 2 users. It prints the counts per city.

Ruby on Rails

class User < ApplicationRecord
end

# Count users per city where count is more than 2
result = User.group(:city).having('count(*) > 2').count
puts result

Important Notes

Use having only after group because it filters groups, not individual rows.

Conditions in having are raw SQL strings, so be careful with syntax.

You can combine select to choose specific columns or aggregates.

Summary

Group organizes data by shared values.

Having filters those groups based on conditions.

Use them together to summarize and filter grouped data easily.