Rails, Count and Group

I have a table like:

 +--------+-----------+-------+-----------+
 |house_no|house_alpha|flat_no|street_name|
 +--开发者_Go百科------+-----------+-------+-----------+
 |       1|           |       |James St   |
 |       1|           |       |James St   |
 |       1|           |       |James St   |
 |       2|          A|       |James St   |
 |       2|          B|       |James St   |
 |       3|          A|       |James St   |
 |       4|           |    416|James St   |
 |       4|           |    416|James St   |
 +--------+-----------+-------+-----------+

And I'm trying to count the number of different addresses in this table. This returns the distinct addresses:

Address.all(:select => 'street_name, flat_no, house_no, house_alpha',
            :group => 'street_name, flat_no, house_no, house_alpha').length

But I want to do it on the SQL end. and trying to combine count and group doesn't like me. I'm clearly doing something wrong.

(Database is postgres, rails is 2.x).

For rails 3 with Arel, you could do the following:

Address.group(:street_name, :flat_no, :house_no, :house_alpha).having("count(*) > 0").count.size

I'm not sure that there's a pretty Rails way to do a count across grouped columns. There are plenty of weird ways to do this in SQL, but this way is easy enough to follow.

Address.find(:all, 
             :select => "count(*) as addr_count", 
             :having => "addr_count > 0", 
             :group => 'street_name, flat_no, house_no, house_alpha').size

That will run the following SQL query.

SELECT count(*) as addr_count FROM "addresses" GROUP BY street_name, flat_no, house_no, house_alpha HAVING addr_count > 0

Edit: Read this for Postgres

From the comments below, here is the way to do the above on Postgres.

Address.find(:all, 
             :select => "count(*)", 
             :having => "count(*) > 0", 
             :group => 'street_name, flat_no, house_no, house_alpha').size

This generates the following query.

SELECT count(*) FROM "addresses" GROUP BY street_name, flat_no, house_no, house_alpha HAVING count(*) > 0

In Rails 2, I have found the :group option to not behave reliably (in my latest case, it made an array of only the first record in each group). As an alternative, you might combine your db search with EnumerableArray#group_by as follows:

addresses = Address.find(:all, 
             :select => "count(*) as addr_count", 
             :having => "addr_count > 0"), 
grouped_addrs = addresses.group_by{|a| [a.street_name, a.flat_no, a.house_no, a.house_alpha] }