Rails - Apr 2010 - mysql syntax error when trying to capture cumulative sum

Hey all,

I''ve searched the web and cannot find an example of getting a
cumulative
count by month for a specific object using Rails, and then displaying
that in an array. This gives a mysql syntax error:

    count = Student.count("SELECT
        x1.MonthNo
      , x1.MonthName
      , x1.Added
      , SUM(x2.Added) AS RunningTotal
      FROM
      (
      SELECT
        MONTH(passed_on) AS MonthNo
      , MONTHNAME(passed_on) AS MonthName
      , COUNT(*) AS Added
      FROM students
      WHERE passed_on >= ''2009-09-23''
      GROUP BY MONTH(passed_on)
      ) AS x1
      INNER JOIN (
      SELECT
        MONTH(passed_on) AS MonthNo
      , MONTHNAME(passed_on) AS MonthName
      , COUNT(*) AS Added
      FROM students
      WHERE passed_on >= ''2009-09-23''
      GROUP BY MONTH(passed_on)
      ) AS x2
      ON x1.MonthNo >= x2.MonthNo
      GROUP BY x1.MonthNo;")

def panels
                  :series => [{
                      :name => ''Passed'',
                      :data => count
                  }
end

The idea is to show the cumulative sum over several months which will be
displayed in a chart. Thanks for any response.
-- 
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Apr 6, 12:19 am, John Merlino
<li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>
wrote:
> Hey all,
>
> I''ve searched the web and cannot find an example of getting a
cumulative
> count by month for a specific object using Rails, and then displaying
> that in an array. This gives a mysql syntax error:
>
count is expecting a rather simpler situation. If you''re supplying a
full sql statement you should use count_by_sql (and you might actually
want to use connection.select_all in this case).

Fred

-- 
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Frederick Cheung wrote:
> On Apr 6, 12:19�am, John Merlino
<li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:
>> Hey all,
>>
>> I''ve searched the web and cannot find an example of getting a
cumulative
>> count by month for a specific object using Rails, and then displaying
>> that in an array. This gives a mysql syntax error:
>>
> 
> count is expecting a rather simpler situation. If you''re supplying
a
> full sql statement you should use count_by_sql (and you might actually
> want to use connection.select_all in this case).
> 
> Fred
Initially, I felt this would be more efficient:

def sum
   a =  Student.passed_on
   a.inject(0) { |s,v| s += v }

  end

But this gives me undefined method "passed_on" and undefined method 
"inject".
-- 
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.