Select SUM(?) Query


Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

Make a quick test in Rails console, you will understand the result of
find_by_sql. Or, you can have a look at
Calculationhttp://techno-weenie.net/blog/main/266/introducing-calculationsplugin.
Once installed, you can use something like:

Order.calculate(:sum, :cost, :group => :country,
:having => ‘sum(cost) > 50’)

The document uses :group_by, but it should be :group

Easy enough?

On 12/29/05, Dylan M. [email protected] wrote:

 When using PHP, I was able to enter a query against my list of

invoices such as :

Select SUM(amount) from invoices where project=“123”

This would give me the total amount of all invoices for project 123.

In rails, how would I do this? I tried using “find_by_sql,” but couldn’t
figure out how to extract the results out of this. Thanks!

Dylan M. <dylan@…> writes:

Select SUM(amount) from invoices where project=“123”

Try:
SELECT SUM(amount) as totalamount FROM invoices WHERE project=“123”

In your result hash, grab the totalamount field.

-damon

On 12/29/05, Dylan M. [email protected] wrote:

When using PHP, I was able to enter a query against my list of invoices
such as :

Select SUM(amount) from invoices where project=“123”

This would give me the total amount of all invoices for project 123.

In rails, how would I do this? I tried using “find_by_sql,” but couldn’t
figure out how to extract the results out of this. Thanks!

One way is:
Invoice.connection.select_value “select sum(amount) from invoices
where project_id = 123”
Unlike find_by_sql, this doesn’t return an instance of ‘Invoice’… it
simply returns the value from the query, or nil if there were no
results

If you’re expecting an integer as the result (e.g. count(*) from blah
where something), you can use Invoice.count_by_sql()
That would give you inaccurate results for ‘amount’, though, if
pennies are involved.