How to include SUM() and COUNT() in a CodeIgniter active record query

Question

I have this method in my Model

public function get_users_details()
{
    $this->db->select("a.*,sum('b.downloads') as downloads,COUNT('b.user_email') as uploads");
    $this->db->from('user a');
    $this->db->join('files b', 'a.email = b.user_email', 'inner');
        
    $query = $this->db->get();
    if ($query->num_rows() > 0) {
        foreach ($query->result_array() as $row) {
            $data[] = $row;
        }
        $query->free_result();
        return $data;
    } else {
        return false;
    }
}

It only returns values from a single row while actually it supposed to return values from multiple rows.

Don Bhrayan Singh · Accepted Answer · 2018-08-09 10:39:15Z

3

sum() and count() are aggregate functions and will only return 1 row unless you combine it with a group_by statement.

SELECT count(*) FROM table_a

will return the total number of rows in table_a.

SELECT table_a.email, count(*) FROM table_a GROUP BY table_a.email

will return the total number of rows PER email address.

In codeigniter 3, we use

$this->db->group_by("table_a.email");

answered Aug 9, 2018 at 10:39

Don Bhrayan Singh

4854 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mickmackusa · Accepted Answer · 2025-06-20 11:27:35Z

There is much to explain regarding your text and your coding attempt.

Fundamentally, and as @Don already mentioned, without an explicit GROUP BY clause in your query, all qualifying rows will be piled into a single aggregated data set and then the SELECT clause will tease out what you tell it to. Effectively, there will never be more than one row.
You have inappropriate quoting in your aggregate functions. Instead of accessing columns by those column names, the query will try to sum and count the literal string values that you have written. Better let CodeIgniter automatically apply the appropriate quotes for your driver/dialect.
If your query should return potentially no sum/count for user records which don't relate to any files record, use a LEFT JOIN, otherwise use a standard/inner JOIN.
I never recommend returning false from a model method which otherwise returns a 2d array. Unconditionally return the empty or 2d array provided by result_array().
It is not necessary to free the result object.

public function getUsersDetails(): array
{
    return $this->db
        ->select('a.*')
        ->select_sum('b.downloads')
        ->select('COUNT(b.user_email) uploads')
        ->from('user a')
        ->join('files b', 'a.email = b.user_email', 'left')
        ->group_by('a.email')
        ->get()
        ->result_array();
}

Collectives™ on Stack Overflow

How to include SUM() and COUNT() in a CodeIgniter active record query

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related