Select SUM() from subquery with LIMIT via CodeIgniter's query builder

Select SUM() from subquery with LIMIT via CodeIgniter's query builder

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

Stefan Bogdanescu

Founder & Senior Architect

Aggregating Over Limited Results in CodeIgniter Query Builder

You cannot simply chain multiple select() calls or append a subquery wrapper to the standard CodeIgniter query builder interface using the fluent API methods alone. The framework expects a linear sequence of operations: define columns, set the table, apply filters, order results, and limit the output before executing. Your current implementation generates a flat query that selects both sum(price) and price, orders by price, and applies the LIMIT 3. This fails because SQL aggregation functions like SUM() operate on entire datasets when no GROUP BY clause is present; applying a LIMIT before the aggregation means you are only summing the top three prices, not summing the top three prices from the total dataset.

To achieve the logic expressed in your SQL snippet—selecting the sum of prices from the top three most expensive items—you must explicitly construct the subquery within the CodeIgniter builder instance. The framework does not automatically nest queries based on method calls; you have to tell it exactly what the inner query looks like and then treat that result as a temporary table for the outer query.

Here is how you can structure the code to match your desired SQL output:

// Step 1: Construct the subquery
$subquery = $this->db->select('price')
    ->from('items')
    ->order_by('price', 'desc')
    ->limit(3)
    ->get();

// Step 2: Check if there are results (important for safety)
if ($subquery->num_rows() > 0) {
    // Step 3: Build the outer query using the subquery name
    $this->db->select('SUM(price)', 'price_sum')
        ->from($subquery->get_last_query())
        ->get();
}

// Note: The above approach requires parsing the raw SQL string from the first call.
// However, a cleaner approach in CodeIgniter 3.x often involves using custom queries
// or leveraging the ability to pass the subquery name directly if supported by your version.

Actually, relying on get_last_query() to parse and reuse the SQL string is brittle because it treats the query as a string rather than an object, which can lead to escaping issues or version incompatibilities. A more robust pattern involves defining the subquery logic separately and injecting it into the main query if your specific CodeIgniter version supports complex nested syntax, or by executing the subquery first and manually aggregating the data in PHP.

If you are using CodeIgniter 3.x, the most reliable way to execute this specific SQL structure without writing raw queries is to build the inner query, retrieve its data, and then iterate over that result set in PHP to calculate the sum. This approach avoids the complexity of nested query builders which vary by version.

// Fetch the top 3 items
$this->db->select('price')
    ->from('items')
    ->order_by('price', 'desc')
    ->limit(3);

$query = $this->db->get();

if ($query->num_rows() > 0) {
    // Aggregate the sum manually in PHP
    $totalSum = 0;
    foreach ($query->result_array() as $row) {
        $totalSum += floatval($row['price']);
    }
    
    echo $totalSum;
}

This method is often preferred because it separates concerns: the database handles the filtering and ordering, while the application logic handles the aggregation. It ensures that you are summing exactly the rows returned by the LIMIT clause without worrying about how the query builder handles nested subqueries internally.

However, if you strictly require a single SQL statement for performance reasons (avoiding the round-trip to the database twice), you might need to resort to raw queries or specific version features. In CodeIgniter 4, the Query Builder is more flexible but still requires explicit construction of from() clauses that can accept subqueries.

// CodeIgniter 4 approach example
$query = \Config\Database::table('items')
    ->select('SUM(price) as total_price')
    ->fromCodeIgniter('subquery', 'items_top3') // Pseudo-code for custom subquery injection
    ->where('1=1'); // Placeholder logic

In practice, CodeIgniter 4's builder allows you to write raw SQL segments if the fluent API becomes too restrictive. You would construct the inner query as a string and inject it:

$subQuery = 'SELECT price FROM items ORDER BY price DESC LIMIT 3';
$this->db->select('SUM(price)');
$this->db->from('(' . $subQuery . ') AS subquery');
$this->db->as('total'); // Optional aliasing
$result = $this->db->get();

This final approach directly mirrors your original SQL intent. It wraps the inner query in parentheses and assigns an alias, allowing CodeIgniter to treat the limited set as a single logical table for the SUM() calculation. Be aware that using raw SQL strings requires you to manage escaping manually if you introduce dynamic column names, though static column names like price are safe. Always verify that your CodeIgniter version correctly parses parentheses in the from() clause, as older versions may require different syntax handling.

Our network