Select grouped column values and their counts via query with JOIN and GROUP BY clauses in CodeIgniter

Select grouped column values and their counts via query with JOIN and GROUP BY clauses in CodeIgniter

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

Stefan Bogdanescu

Founder & Senior Architect

Select grouped column values and their counts via query with JOIN and GROUP BY clauses in CodeIgniter

You are attempting to aggregate data from two tables, tblSaler and tblProduct, to get a count of products per salesperson. Your goal is to produce a result set where each salesperson's name appears once alongside the total number of products they have sold.

The issue with your current approach lies in the structure of your SQL generation logic within CodeIgniter. You are trying to inject hardcoded aliases and WHERE clauses directly into the select() method string:

$this->db->select('count(SalerName) as sothorn where tblSaler.SalerID = 1...');

This syntax is fundamentally incorrect for two reasons. First, SQL keywords like WHERE cannot appear inside a SELECT clause; they belong in the WHERE or JOIN context. Second, you are hardcoding specific salesperson IDs (1, 2, 3, 4) and names directly into the query string. This approach makes the code brittle, difficult to maintain, and prone to SQL injection if the data were dynamic. It also fails because a single SELECT statement cannot contain multiple distinct filtering conditions like that without combining them with logical operators, which you aren't doing correctly here.

To solve this, you need to construct a query that groups the joined results by the salesperson entity and applies an aggregate function over the product count. The standard relational database pattern for this is a JOIN followed by a GROUP BY.

Here is how you should structure the CodeIgniter Active Record call:

$this->db->select('tblSaler.SalerName, COUNT(tblProduct.ProductID) as TotalProducts');
$this->db->from('tblSaler');
$this->db->join('tblProduct', 'tblSaler.SalerID = tblProduct.SalerID', 'inner');
$this->db->group_by('tblSaler.SalerName');
$query = $this->db->get();

$results = $query->result_array();

In this corrected implementation, we select the SalerName and count the associated ProductIDs. The critical component is the GROUP BY clause. When you group by tblSaler.SalerName, the database engine aggregates all rows sharing that name into a single row. Because your join condition matches SalerID, each salesperson will appear in the product table exactly as many times as they have products. The COUNT() function then tallies these occurrences, returning the final count for that specific group.

One important technical detail to consider is data integrity. If a salesperson exists in tblSaler but has no matching records in tblProduct, an INNER JOIN (as used above) will exclude them from the result entirely. If you need to list every salesperson regardless of whether they have sold products, you must change the join type to a LEFT JOIN. Additionally, grouping by the name string is generally safe, but if your application relies on SalerID for unique identification and names are not guaranteed to be unique (though unlikely in this schema), it is safer to group by the primary key, tblSaler.SalerID, to ensure consistent aggregation behavior across different database engines.

When writing queries in CodeIgniter, always rely on the query builder's methods like group_by(), where(), and join() rather than injecting raw SQL strings into the select method. This ensures your query remains readable, performant, and secure. If you require more complex aggregation logic or need to optimize performance for large datasets, you might consider leveraging specialized resources for advanced database patterns in PHP frameworks.

Our network