I want to convert following query into pure ruby one i.e. by using find
@us_state_sales=SalesReport.find_by_sql("SELECT z.state,
sum(s.royalty_price) as royalty_price, sum(s.units) as units FROM
sales_reports s, zip_codes z
WHERE royalty_currency = ''USD'' &&
z.zip_code=s.postal_code GROUP BY
z.state ORDER BY royalty_price desc LIMIT 10")
i try something like this
@us_state_sales1=SalesReport.find(:all, :select=>" zip_codes.state,
sum(sales_reports.royalty_price) as royalty_price,
sum(sales_reports.units) as units",
:joins=>''zip_codes'',
:conditions=>["royalty_currency = ''USD'' &&
zip_codes.zip_code=s.postal_code"],
:group=> ''zip_codes.state'',
:order=> ''royalty_price desc'',
:limit=> 10)
but it gives me the following error ......
Mysql::Error: Unknown column ''zip_codes.state'' in
''field list'': SELECT
zip_codes.state,sum(sales_reports.royalty_price) as royalty_price,
sum(sales_reports.units) as units FROM `sales_reports` zip_codes WHERE
(download_date BETWEEN ''2010-01-01 00:00:00'' AND
''2010-12-31 00:00:00''
&& artist_name in ("Coconut Records","Reeve
Oliver","Weezer") &&
royalty_currency = ''USD'' &&
zip_codes.zip_code=s.postal_code) GROUP BY
zip_codes.state ORDER BY royalty_price desc LIMIT 10
Regards,
Salil
--
Posted via http://www.ruby-forum.com/.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
