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