#5721 count with group by discards :distinct option - Ruby on Rails

Type	To find
responsible:me	tickets assigned to you
tagged:"@high"	tickets tagged @high
milestone:next	tickets in the upcoming milestone
state:invalid	tickets with the state invalid
created:"last week"	tickets created last week
sort:number, importance, updated	tickets sorted by #, importance or updated
Combine keywords for powerful searching.
Use advanced searching »

This project is archived and is in readonly mode.

#5721 ✓resolved

count with group by discards :distinct option

Reported by Julien Portalier | September 28th, 2010 @ 10:10 AM | in 3.0.2

A simple count does generate the correct SQL :

Post.count(:user_id, :distinct => true)
# SELECT COUNT(DISTINCT "posts"."user_id") AS "count_user_id" FROM "posts"

But mixing group and count discards the :distinct option:

Post.group(:date).count(:user_id, :distinct => true)
# SELECT COUNT("posts"."user_id") AS "count_user_id", date AS date
# FROM "posts" GROUP BY date

Comments and changes to this ticket

Neeraj Singh September 28th, 2010 @ 09:12 PM
- State changed from “new” to “open”
- Importance changed from “” to “Low”
Neeraj Singh September 28th, 2010 @ 09:17 PM
I am not fully sure what value distint will bring in this case since grouping is always by same vlaue.

In this case I have three cars with names: honda, toyota and toyota.
```
ree-1.8.7-2010.02 > Car.group(:name).count(:name, :distinct => true)
  SQL (0.3ms)  SELECT COUNT("r_cars"."name") AS count_name, name AS name FROM "r_cars" GROUP BY name
 => #<OrderedHash {"honda"=>1, "toyota"=>2}>
```
By distinct you mean the count for toyota should be '1' ?

Julien Portalier September 29th, 2010 @ 11:54 AM

Here is an example: I have posts published by users, and I want to know how many different users are publishing posts everyday.

Post.create(:user_id => 1, :created_on => '2010-09-29')
Post.create(:user_id => 1, :created_on => '2010-09-29')
Post.create(:user_id => 2, :created_on => '2010-09-29')
Post.create(:user_id => null, :created_on => '2010-09-29')

Post.group(:created_on).count)
# => {'2010-09-29' => 4}

Post.group(:created_on).count(:user_id)
# => {'2010-09-29' => 3}

Post.group(:created_on).count(:user_id, :distinct => true)
# => {'2010-09-29' => 2}

From the MySQL documentation:

COUNT(*) counts the total number of rows.
COUNT(column) counts rows with non null column.
COUNT(DISTINCT column) counts rows with different, non null column.

You flagged this item as spam.
Marcelo Giorgi October 1st, 2010 @ 12:40 PM
- Assigned user set to “Santiago Pastorino”
- Tag set to “activerecord calculation, patched”
Hi,

I implemented a test and patch for this.

This applies cleanly to master now.
- honor-distinct-option-when-count-operation-is-used-after-group-clause.patch 3.5 KB
You flagged this item as spam.
Andrea Campi October 11th, 2010 @ 07:21 AM
- Tag changed from “activerecord calculation, patched” to “activerecord calculation, patch”
bulk tags cleanup
Santiago Pastorino October 11th, 2010 @ 01:48 PM
- Milestone cleared.
- Assigned user changed from “Santiago Pastorino” to “Aaron Patterson”
Repository October 11th, 2010 @ 05:59 PM
- State changed from “open” to “resolved”
(from [dba7de0da0af6732c30484e8e16f22614cfc13f2]) Honor distinct option when used with count operation after group clause [#5721 state:resolved] http://github.com/rails/rails/commit/dba7de0da0af6732c30484e8e16f22...
Repository October 11th, 2010 @ 05:59 PM
(from [1c9022de212c190362d40b98624dcf71d20ca073]) Honor distinct option when used with count operation after group clause [#5721 state:resolved] http://github.com/rails/rails/commit/1c9022de212c190362d40b98624dcf...
Jeremy Kemper October 15th, 2010 @ 11:02 PM
- Milestone set to “3.0.2”
[bulk edit]
Ryan Bigg October 16th, 2010 @ 02:22 AM
- Tag changed from “activerecord calculation, sheepskin boots, patch” to “activerecord calculation patch”
Automatic cleanup of spam.