CODEWITHSUNDEEP
ruby-on-rails-3postgresqlrails-postgresql
jvatic
jvatic

Reputation: 4046

Postgresql (Rails 3) merge rows on column (same table)

First, I've been using mysql for forever and am now upgrading to postgresql. The sql syntax is much stricter and some behavior different, thus my question.

I've been searching around for how to merge rows in a postgresql query on a table such as

id      | name        |   amount
0       | foo         | 12
1       | bar         | 10
2       | bar         | 13
3       | foo         | 20

and get

name        |   amount
foo         | 32
bar         | 23

The closest I've found is Merge duplicate records into 1 records with the same table and table fields

sql returning duplicates of 'name':

 scope :tallied, lambda { group(:name, :amount).select("charges.name AS name,
                              SUM(charges.amount) AS amount,
                              COUNT(*) AS tally").order("name, amount desc") }


What I need is

 scope :tallied, lambda { group(:name, :amount).select("DISTINCT ON(charges.name) charges.name AS name,
                              SUM(charges.amount) AS amount,
                              COUNT(*) AS tally").order("name, amount desc") }

except, rather than returning the first row of a given name, should return mash of all rows with a given name (amount added)

In mysql, appending .group(:name) (not needing the initial group) to the select would work as expected.

This seems like an everyday sort of task which should be easy. What would be a simple way of doing this? Please point me on the right path.

P.S. I'm trying to learn here (so are others), don't just throw sql in my face, please explain it.

Upvotes: 1

Views: 1262

Answers (1)

Denis de Bernardy
Denis de Bernardy

Reputation: 78463

I've no idea what RoR is doing in the background, but I'm guessing that group(:name, :amount) will run a query that groups by name, amount. The one you're looking for is group by name:

select name, sum(amount) as amount, count(*) as tally
from charges
group by name

If you append amount to the group by clause, the query will do just that -- i.e. count(*) would return the number of times each amount appears per name, and the sum() would return that number times that amount.

Upvotes: 1

Related Questions