# Mysql syntax error when trying to capture cumulative sum

**URL:** <https://www.ruby-forum.com/t/mysql-syntax-error-when-trying-to-capture-cumulative-sum/186380>\
**Category:** Rails\
**Created:** [April 6, 2010, 1:19am UTC](https://www.ruby-forum.com/t/mysql-syntax-error-when-trying-to-capture-cumulative-sum/186380 "2010-04-06T01:19:00Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![jmaenpaa](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/9de053/32.png) [@jmaenpaa](https://www.ruby-forum.com/u/jmaenpaa)\
**Post date:** [April 6, 2010, 1:19am UTC](https://www.ruby-forum.com/t/mysql-syntax-error-when-trying-to-capture-cumulative-sum/186380/1 "2010-04-06T01:19:00Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![jmaenpaa](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/9de053/32.png) [@jmaenpaa](https://www.ruby-forum.com/u/jmaenpaa)\
**Post date:** [April 6, 2010, 1:50am UTC](https://www.ruby-forum.com/t/mysql-syntax-error-when-trying-to-capture-cumulative-sum/186380/2 "2010-04-06T01:50:39Z")

</div>

On Apr 6, 12:19 am, John M. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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

---

<div class="post-metadata">

**Author:** ![jmaenpaa](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/9de053/32.png) [@jmaenpaa](https://www.ruby-forum.com/u/jmaenpaa)\
**Post date:** [April 6, 2010, 4:35pm UTC](https://www.ruby-forum.com/t/mysql-syntax-error-when-trying-to-capture-cumulative-sum/186380/3 "2010-04-06T16:35:28Z")

</div>

Frederick C. wrote:

> On Apr 6, 12:19�am, John M. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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”.