Count numerical values in query CakePHP

Question

I try to count numerical values in CakePHP according the documentation. Whatever I try, I only receive the number of rows for this column and not the sum of the numerical values.

Queries in my Article Model:

I try to count the number of hitcounts of all articles in my database. The hitcount is a INT field:

$total_articles = $this->find('count', array('fields' => 'Article.hitcount'));

//returns 3, the total number of rows in the database

I try to count the number of hitcounts from a particular user

$hitcountUser = $this->find('count', array('fields' => 'Article.hitcount', 'conditions' => array('Article.user_id' => $user)));

//returns 3, the total number of rows in the database, all articles are posted by this user.

How can I sum up the numerical values in Article.hitcount? I have googled but can't find the answers.

What about searching on stackoverflow ? ;-) stackoverflow.com/questions/4971148/… — nIcO

Scott Harwell Scott Harwell · Accepted Answer · 2011-11-16T15:27:41

Count does not sum the values from the field that you are retrieving. It performs a MySQL count which counts the number of rows returned in the result.

In order to sum a field, you have to return the MySQL SUM value or loop through the results you got in PHP and sum them.

I would do this:

$article = $this->Model->find(
    'first',
    array(
        'fields'=>array('SUM('Article.hitcount') AS Article.hitcountsum'),
    )
);

Which should give you an array like:

$article['Article']['hitcountsum'];

Count numerical values in query CakePHP

1 Answers