How to count group by rows in rails?

Question

When I use User.count(:all, :group => "name"), I get multiple rows, but it's not what I want. What I want is the count of the rows. How can I get it?

Solution 1

Currently (18.03.2014 - Rails 4.0.3) this is correct syntax:

Model.group("field_name").count

It returns hash with counts as values e.g.

SurveyReport.find(30).reports.group("status").count

#=> {
  "pdf_generated" => 56
}

Solution 2

1. User.count will give you the total number of users and translates to the following SQL: SELECT count(*) AS count_all FROM "users"
2. User.count(:all, :group => 'name') will give you the list of unique names, along with their counts, and translates to this SQL: SELECT count(*) AS count_all, name AS name FROM "users" GROUP BY name

I suspect you want option 1 above, but I'm not clear on what exactly you want/need.

Solution 3

Probably you want to count the distinct name of the user?

User.count(:name, :distinct => true)

would return 3 if you have user with name John, John, Jane, Joey (for example) in the database.

 ________
| name   |
|--------|
| John   |
| John   |
| Jane   |
| Joey   |
|________|

Solution 4

Try using User.find(:all, :group => "name").count

Good luck!

Solution 5

I found an odd way that seems to work. To count the rows returned from the grouping counts.

User Table Example

________
| name   |
|--------|
| Bob    |
| Bob    |
| Joe    |
| Susan  |
|________|

Counts in the Groups

User.group(:name).count

# SELECT COUNT(*) AS count_all
# FROM "users"
# GROUP BY "users"."name"

=> {
  "Bob" => 2,
  "Joe" => 1,
  "Susan" => 1
}

Row Count from the Counts in the Groups

User.group(:name).count.count

=> 5

Something Hacky

Here's something interesting I ran into, but it's quite hacky as it will add the count to every row, and doesn't play too well in active record land. I don't remember if I was able to get this into an Arel / ActiveRecord query.

SELECT COUNT(*) OVER() AS count, COUNT(*) AS count_all
FROM "users"
GROUP BY "users"."name"

[
  { count: 3, count_all: 2, name: "Bob" },
  { count: 3, count_all: 1, name: "Joe" },
  { count: 3, count_all: 1, name: "Susan" }
]

View more solutions

Comments

[Simone Carletti]

possible duplicate of Counting and grouping at the same time

[user199403]

thanks hgimenez, what i want is select count(*) as count_all) from (select name as name from users group by name)

[hgmnz]

In that case, what Sikachu's answer seems more appropriate - you want the number of unique names, and that'll do.