Group by and Count Using Activerecord
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...
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?
4 Answers
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
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.
another option:
Person.select(:name, 'COUNT(name)').group(:name)
this generate an array of people with attribute count
Another option:
Person.select(:name).distinct(:name).count
Marcus Vance is a cybersecurity auditor and technology writer dedicated to educating the public about online safety, data privacy regulations, enterprise security, and emerging cyber threats.
