GROUP BY and COUNT using ActiveRecord

Question

Refering to this: Is there any difference between GROUP BY and DISTINCT

Given a table that looks like this:

name
------
barry
dave
bill
dave
dave
barry
john
This query:

SELECT name, count(*) AS count FROM table GROUP BY name;
Will produce output like this:

name    count
-------------
barry   2
dave    3
bill    1
john    1

What is the correct Rails convention here for ActiveModel to perform a GROUP BY with COUNT?

`Table.all.group(:name).count` – Vrushali Pawar Aug 07 '15 at 13:39 — Vrushali Pawar, Aug 07 '15 at 13:39

score 133 · Accepted Answer · edited Mar 02 '20 at 14:55

133

Distinct and Group By are going to give you different results. To get the results you expect you'll want to use

Person.group(:name).count
(1.2ms)  SELECT COUNT(*) AS count_all, name AS name FROM "people" GROUP BY "people"."name"
=> {"Dan"=>3, "Dave"=>2, "Vic"=>1}

Seen above, group will return things as a hash. While distinct just returns the number of people in total, seen below.

Person.distinct(:name).count
(0.4ms)  SELECT DISTINCT COUNT(DISTINCT "people"."id") FROM "people"
=> 6

edited Mar 02 '20 at 14:55

Paweł Gościcki

9,066
5
70
81

answered Aug 07 '15 at 15:29

thedanotto

6,895
5
45
43

which rails version is this? – ant Mar 22 '18 at 03:55
4.2+ from my experience – thedanotto Mar 22 '18 at 03:56
9

4.2+ no need of `all`, simply `Person.group(:name).count` – Amol Pujari Jun 21 '18 at 08:32
5

For anyone looking to reproduce the distinct count query above, `Person.distinct(:name).count` will now return the same as `Person.count`. That's obvsiously not the desired result. These days, you would need to do `Person.distinct.count(:name)`, to get `6`. – zbeat Apr 02 '21 at 17:20

score 7 · Answer 2 · answered Apr 30 '19 at 19:15

Note that the accepted answer will return a hash:

Tagging.joins(:tag).group(:name).size
   (0.4ms)  SELECT COUNT(*) AS count_all, `name` AS name FROM `taggings` INNER JOIN `tags` ON `tags`.`id` = `taggings`.`tag_id` GROUP BY `name`
 => {"applesauce"=>1, "oranges"=>2}

But what if you want to return the count plus some columns from different tables in a join. Then you also need to use the select ActiveRecord query:

collection = Tagging.select('COUNT(*) as total, taggings.taggable_id, taggings.taggable_type').joins(:tag).where(taggable_type: 'LineItem', taggable_id: ['5cad0dcc3ed1496086000031', '5cad0dcd3ed1496086000081'] ).group(:name)

collection.each do |item|
  puts item.taggable_id
  puts item.total
end

5cad0dcc3ed1496086000031
1
5cad0dcc3ed1496086000031
2

With this second approach, you can fetch additional details about a join relationship without any additional queries or loop constructs.

score 6 · Answer 3 · answered Aug 03 '20 at 16:36

6

another option:

Person.select(:name, 'COUNT(name)').group(:name)

this generate an array of people with attribute count

answered Aug 03 '20 at 16:36

Jorge Garay Chandia

71
1
1

score 0 · Answer 4 · answered Dec 17 '21 at 20:37

0

Another option:

Person.select(:name).distinct(:name).count

answered Dec 17 '21 at 20:37

Cesar Hernandez

11
1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Dec 17 '21 at 20:39

score 0 · Answer 5 · answered Mar 16 '23 at 13:10

SELECT name, count(*) AS count FROM table GROUP BY name;

In Rails you'd write it like this: Model.group(:name).count

You can also order by the count by doing Model.group(:name).order(:count_id).count. Model.group(:name).order(count_id: :desc).count

Doing a .count on a .joins will give you count_all. Doing a .count on a single table will give you a count_id to order by.

GROUP BY and COUNT using ActiveRecord

5 Answers5

Linked