Rails, Count and Group

Question

I have a table like:

 +--------+-----------+-------+-----------+
 |house_no|house_alpha|flat_no|street_name|
 +--------+-----------+-------+-----------+
 |       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).

Answer 1

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