mysql syntax error when trying to capture cumulative sum

rubyonrails-talk
1

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.

2

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

3

Frederick Cheung 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".