ActiveRecord group and count — SQL GROUP BY in Rails

SQL GROUP BY is one of the most-used aggregation patterns. ActiveRecord supports it directly, but the return type surprises developers coming from SQL.

Basic group and count

SQL:

SELECT status, COUNT(*) FROM orders GROUP BY status

ActiveRecord:

Order.group(:status).count
# => {"pending"=>12, "shipped"=>34, "cancelled"=>5}

The return type is a Hash, not a Relation. You cannot chain further after .count.

Grouping by Multiple Columns

SQL:

SELECT status, country, COUNT(*) FROM orders GROUP BY status, country

ActiveRecord:

Order.group(:status, :country).count
# => {["pending", "US"]=>8, ["pending", "UK"]=>4, ...}

Other Aggregates

Order.group(:status).sum(:total_cents)
Order.group(:status).average(:total_cents)
Order.group(:status).maximum(:total_cents)
Order.group(:status).minimum(:total_cents)

All return a Hash with the group value as key.

Adding HAVING

SQL:

SELECT status, COUNT(*) FROM orders GROUP BY status HAVING COUNT(*) > 10

ActiveRecord:

Order.group(:status).having("COUNT(*) > ?", 10).count

.having takes a string condition with the same sanitization rules as .where. Always use ? placeholders.

Ordering Grouped Results

Order.group(:status).order("count_all DESC").count
# The alias 'count_all' is what Rails uses for COUNT(*)

If you’re ordering by a specific aggregate column:

Order.group(:status).order("sum_total_cents DESC").sum(:total_cents)

With select and group Together

If you need both the grouped data and other columns:

Order.select("status, COUNT(*) as order_count, SUM(total_cents) as revenue")
     .group(:status)
# Returns a Relation with virtual attributes order_count and revenue

Access them:

results = Order.select("status, COUNT(*) as order_count").group(:status)
results.each { |r| puts "#{r.status}: #{r.order_count}" }

Grouping by Date

SQL:

SELECT DATE(created_at), COUNT(*) FROM orders GROUP BY DATE(created_at)

ActiveRecord:

Order.group("DATE(created_at)").count
# => {"2026-09-01"=>45, "2026-09-02"=>62, ...}

For PostgreSQL, DATE_TRUNC is more precise:

Order.group("DATE_TRUNC('month', created_at)").count

The N+1 You Don’t Expect

Avoid this pattern:

Status.all.each do |status|
  count = Order.where(status: status.name).count
  # Separate query per status — this is N+1
end

Replace with:

Order.group(:status).count
# One query. Use the hash.

What Rails Generates

Order.group(:status).count
# SELECT status, COUNT(*) AS count_all FROM orders GROUP BY status

Always verify with .to_sql before adding .count (which forces execution):

Order.group(:status).to_sql

Quick Reference