Select SUM() from suquery with LIMIT via Codeigniter's active record methods

Question

I have a query like this, I want to use this query in Codeigniter.

SELECT sum(price) 
FROM (SELECT price
      FROM items
      ORDER BY price DESC
      LIMIT 3
) AS subquery;

I have did

$this->db->select('SUM(price)');
$this->db->select('price');
$this->db->from('items');
$this->db->order_by('price desc');
$this->db->limit(3);
$this->db->get();

This gives output like this

SELECT sum(price), price
FROM items
ORDER BY price DESC
LIMIT 3;

What can I do to select the sum from the limited and ordered subquery?

Languish

is ur raw query works fine ? – Dustheap

My raw query works well – Languish

you can use CodeIgniter-Subqueries . if you are using CI3 take a look at Query grouping – Witted

Answer 1

Use like this

$this->db->select_sum('price');
$this->db->select('price');
$this->db->from('items');
$this->db->order_by('price desc');
$this->db->limit(3);
$this->db->get();

Correspondence

I didn't get expected output – Languish

what result you want? – Correspondence

when I try your query it will gives the output like this SELECT sum(price), price FROM items ORDER BY price DESC LIMIT 3; – Languish

Answer 2

you can use a query like this

$this->db->select_sum('price');
$this->db->from('items');
$this->db->order_by('price desc');
$this->db->limit(3);
$this->db->get();

If you want to put data into an array then:

 $data=$this->db
    ->select_sum('price')
    ->from('items')
     ->order_by('price desc')
    ->limit(3)
    ->get();
return $data->result_array();

Infuriate

Answer 3

make it simple if your query is working fine.

$query = $this->db->query('SELECT sum(price) FROM (SELECT price FROM items ORDER BY price DESC LIMIT 3 ) AS subquery');
print_r($query->result_array());

Dustheap

Actually this is not my query, I have a big table with join in my module. I have just asked a simple item. If I try your query It'll take 50 lines. – Languish

Answer 4

To implement CodeIgniter's active record query building methods, prepare and compile the subquery, then execute a parent query using select_sum() and feed the subquery into the get() call.

In both query building chains, the from() method can be omitted because the table name can be passed in via get_compiled_select() and the subquery can be passed in via get().

I have tested the following to work successfully in a CI3 application.

public function sumTopThreePrices()
{
    $sub = $this->db
        ->select('price')
        ->order_by('price', 'DESC')
        ->limit(3)
        ->get_compiled_select('items');

    return $this->db
        ->select_sum('price')
        ->get("($sub) sub")
        ->row()
        ->price;
}

Rendered SQL: (quoting may differ depending on dialect/database)

SELECT SUM("price") AS "price"
FROM (SELECT "price"
FROM "items"
ORDER BY "price" DESC
 LIMIT 3) sub

Bomar

Answer 5

 public function advanceSalary($id) {
        if ($id) {
            $this->db->select('salaryLaser.*');
            //$this->db->select_sum('salaryLaser.credit');
            $this->db->select('SUM(salaryLaser.credit) as creditTotal');
            $this->db->select('SUM(salaryLaser.debit) as debitTotal');
            $this->db->from($this->salaryLaser);
            $this->db->where('salaryLaser.employeeId', $id);
            $this->db->where('salaryLaser.employeeRole', '1');
            $advance = $this->db->get();
            if ($advance->num_rows() > 0) {
                return $advance->row();
            } else {
                return FALSE;
            }
        } else {
            return FALSE;
        }
    }

Establishment

Answer 6

Try this to fix it:

 /**
  * [total_currency description]
  * @param  [type] $column_name [description]
  * @param  [type] $where       [description]
  * @param  [type] $table_name  [description]
  * @return [type]              [description]
  */

function total_count($column_name,  $where, $table_name)
{
   $this->db2->select_sum($column_name);
    // If Where is not NULL
    if(!empty($where) && count($where) > 0 )
    {
       $this->db2->where($where);
    }

      $this->db2->from($table_name);
        // Return Count Column
return $this->db2->get()->row($column_name);//table_name array sub 0




 }

Gowrie