current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have the following line of code which works in sqlite, but in PostGres doesn't:

@income_by_category = SoldCategory.group(:category_name).having("branch_id=?", @branch_id).sum(:total).to_a

It's basically grouping all the SoldCategory by :category_name that have the same @branch_id, adding all their :total's, and converting it to an array. I'm using it to provide some data to some charts. It gives me a result similar to this: 

[['shoes', 5000], ['pants', 6000], ['shirts', 7000]]

How can I achieve the same result using PostGress?

share|improve this question
    
What SQL is ActiveRecord generating? –  Georges Sep 17 '13 at 21:49

1 Answer 1

up vote 1 down vote accepted

So you want to take all the sold_categories that have a given branch_id and then find the per-category sums? Your approach is a bit off, HAVING is not what you're looking for. The SQL you want is simply this:

select category_name, sum(total)
from sold_categories
where branch_id = ?
group by category_name

and that translates to this with ActiveRecord:

SoldCategory.where(:branch_id => @branch_id).sum(:total, :group => :category_name)

That will give you a nice Hash that looks like this:

{ 'shoes' => 5000, 'pants' => 6000, 'shirts' => 7000 }

Once you have this fixed, your very next step should be to install PostgreSQL in your development environment so that you can develop and deploy using the same database. There are a lot of small differences between databases that no ORM can protect you from; database portability is a myth unless your needs are absurdly simple or you write your own portability layer and test test test test test and test some more and then run more tests to make sure you've tested every code path you use on every database you care about.

share|improve this answer
    
That worked perfectly! thank you!! –  Mark-AR Sep 17 '13 at 22:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.