# How to convert the find\_by\_sql code to the find code

**URL:** <https://www.ruby-forum.com/t/how-to-convert-the-find-by-sql-code-to-the-find-code/165700>\
**Category:** Rails\
**Created:** [April 16, 2009, 3:33pm UTC](https://www.ruby-forum.com/t/how-to-convert-the-find-by-sql-code-to-the-find-code/165700 "2009-04-16T15:33:55Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![salil](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/c89c15/32.png) [@salil](https://www.ruby-forum.com/u/salil)\
**Post date:** [April 16, 2009, 3:33pm UTC](https://www.ruby-forum.com/t/how-to-convert-the-find-by-sql-code-to-the-find-code/165700/1 "2009-04-16T15:33:55Z")

</div>

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