Forum: Ruby on Rails dealing with complex query by find_by_sql

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Sijo k. (Guest)
on 2009-04-23 14:05
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
Sijo k. (Guest)
on 2009-04-24 08:46
Hi
    I didnot get an answer to this yet
Sijo
This topic is locked and can not be replied to.