# Dealing with complex query by find\_by\_sql

**URL:** <https://www.ruby-forum.com/t/dealing-with-complex-query-by-find-by-sql/166276>\
**Category:** Rails\
**Created:** [April 23, 2009, 12:05pm UTC](https://www.ruby-forum.com/t/dealing-with-complex-query-by-find-by-sql/166276 "2009-04-23T12:05:21Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![sijo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/da6949/32.png) [@sijo](https://www.ruby-forum.com/u/sijo)\
**Post date:** [April 23, 2009, 12:05pm UTC](https://www.ruby-forum.com/t/dealing-with-complex-query-by-find-by-sql/166276/1 "2009-04-23T12:05:21Z")

</div>

Hi  
I have a query like  
sql = “SELECT count(DISTINCT r.id) as req\_count,  
round(SUM(h.assigned\_days)/count(DISTINCT r.id),1) as  
avg\_days,h.role\_id,EXTRACT(YEAR from r.created\_at)||’-’||EXTRACT(MONTH  
from r.created\_at) as period, max(r.created\_at) as created\_date FROM  
my\_requests r, my\_request\_histories h WHERE r.id \> 0 AND h.my\_request\_id  
= r.id GROUP BY period,h.role\_id”

Here I get results like  
req\_count | avg\_days | role\_id | period | created\_date  
-----------±---------±--------------------±-------±---------------------------  
18 | 0.0 | 11 | 2009-3 | 2009-03-23  
12:50:17.34481  
6 | 0.0 | 12 | 2009-3 | 2009-03-22  
14:29:03.141328  
4 | 0.0 | 13 | 2009-3 | 2009-03-22  
14:29:03.200008  
6 | 0.0 | 14 | 2009-3 | 2009-03-22  
14:29:03.100028  
20 | 2 | 11 | 2009-4 | 2009-04-23  
12:50:17.78481  
7 | 1 | 12 | 2009-4 | 2009-04-22  
14:29:03.297128  
10 | 4 | 13 | 2009-4 | 2009-04-22  
14:29:03.297128  
6 | 0.0 | 14 | 2009-4 | 2009-04-22  
14:29:03.297128

So to get the above I executed like  
records = MyRequest.find\_by\_sql(sql)  
I would like to know how to collect the records from above by  
role and month wise. I need to draw a graph(x axis req\_count and y axis  
avg\_days).For example for role\_id 11 in march 2009 values (18,0.0) and  
for april 2009 values(20,2) Similarly for role\_id 12,13 etc  
here I have to draw 4 graphs That is for role 11,12,13 and 14  
I know how to draw graph but don’t know how to collect the data  
from above array.

Please help  
Thanks in advance  
Sijo

---

<div class="post-metadata">

**Author:** ![sijo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/da6949/32.png) [@sijo](https://www.ruby-forum.com/u/sijo)\
**Post date:** [April 24, 2009, 6:46am UTC](https://www.ruby-forum.com/t/dealing-with-complex-query-by-find-by-sql/166276/2 "2009-04-24T06:46:30Z")

</div>

Hi  
I didnot get an answer to this yet  
Sijo