How to COUNT() aggregate rows when using GROUP BY with CodeIgniter's query builder

How to COUNT() aggregate rows when using GROUP BY with CodeIgniter's query builder

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Counting Rows with GROUP BY in CodeIgniter Query Builder

When you need to aggregate data based on frequency, such as counting how many records belong to each user_id, the standard SQL pattern involves a COUNT() function paired with GROUP BY. In CodeIgniter's Active Record (Query Builder), this translates directly to chaining specific methods that map perfectly to underlying SQL constructs. The challenge often isn't writing the query itself, but ensuring you retrieve the resulting array in the exact shape your application logic expects.

To achieve a top 10 list of users ordered by their entry count, you must construct a query that groups rows by user_id and counts them. In CodeIgniter 3.x, this is done using the group_by() and count() methods available on the DB Query Builder object. The syntax relies on passing the column name for grouping to group_by() and then explicitly telling the builder which field represents the count result.

Consider a scenario where you have a table with columns like id, user_id, and points. If your goal is to return an associative array where keys are user_id and values are the row counts, the standard approach involves selecting the count of all rows while grouping by the specific ID column. You can write this as follows:

$this->db->select('COUNT(id) as total_entries');
$this->db->group_by('user_id');
$query = $this->db->get('your_table_name');

// To get the top 10, you must order by the alias created in the SELECT clause
$query->order_by('total_entries', 'desc')
      ->limit(10);

$result = $query->result();

foreach ($result as $row) {
    // Accessing the count via the alias defined in the query
    echo $row->user_id . ' => ' . $row->total_entries . " rows\n";
}

The critical detail here is how you handle the result set. If you simply run count() without an alias or without ordering, CodeIgniter will return a scalar value representing the total count of the grouped set, not an array of counts per group. By selecting COUNT(id) as total_entries, you create a virtual column in your result set that holds the calculated count for each group. You can then order by this alias and apply a limit to fetch only the top performers.

If you prefer to work with plain arrays rather than objects, you can use result_array(). However, be mindful of how CodeIgniter handles empty groups or NULL values depending on your database driver configuration. In most standard setups, COUNT(id) will return 0 for a group if no rows exist, but since we are grouping by an existing primary key in the dataset, every user_id present in the source table will appear at least once.

A common pitfall is attempting to use count() as a method without defining an alias and then trying to access it directly from the object property. Without the alias, the result structure changes, and accessing $row->total_entries will fail because the property name defaults to something like count or becomes inaccessible in certain contexts depending on the version. Explicitly naming the aggregate field ensures predictable behavior across different CI versions and makes the code more readable.

This pattern works seamlessly with CodeIgniter's database abstraction layer, allowing you to maintain database agnosticism while leveraging SQL's powerful grouping capabilities. For developers looking for further examples of complex query patterns in the CodeIgniter ecosystem, resources like https://laravelcompany.com often provide deep dives into optimizing Active Record usage, though always verify syntax against your specific CI version documentation as method signatures can evolve between major releases.

Our network