CI How to select the last row of another column in a select sum of another column query

Question

+-----+-----+-------+
| PID | Qty | Price |
+-----+-----+-------+
|   1 |   5 |   100 |
|   2 |  10 |   500 |
|   3 |   4 |   300 |
+-----+-----+-------+

$this->db->select("SUM(qty) as total, price as last price");
$this->db->from('table');
$query = $this->db->get();
$result = $query->result();

Now the above code will return as many rows as there is price,

I want to return only one row where there is total of qty and the last value of price

Expected output:

Array (
    [0] => stdClass Object
        (
            [total] => 19
            [last_price] => 300
        )
)

I have a table with thousands of rows of qty and price, but I want to select the total sum of column qty and only the last value of the price, not sum of qty and all rows of price, I want only the last value of price how do I write my select query ?

Thank you in advance.

$this->db->select("SUM(qty) as total, price as last price ORDER BY PID DESC limit 1"); — Death-is-the-real-truth
– Death-is-the-real-truth, Commented Apr 24, 2017 at 9:30

Death-is-the-real-truth · Accepted Answer · 2017-04-24 09:40:13Z

2

You can do it like below:-

$this->db->select("SUM( qty ) AS qty, (SELECT Price FROM  <table name> ORDER BY PID DESC LIMIT 1) AS Price");
$this->db->from('table');

Note:- Instead of <table name> write your actual table name there. Also take care of column-names as well.

answered Apr 24, 2017 at 9:40

Death-is-the-real-truth

72.4k10 gold badges59 silver badges106 bronze badges

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

2 Comments

Charas Over a year ago

Nice, thank you, didn't know you can put another SELECT inside the select statement. Thanks!

Death-is-the-real-truth Over a year ago

@Charas glad to help you.:):)

Darshan Mehta · Accepted Answer · 2017-04-24 09:34:31Z

1

Try this:

SELECT SUM(qty), a.*
FROM products, 
(SELECT price FROM products
ORDER BY pid DESC 
LIMIT 1) a;

answered Apr 24, 2017 at 9:34

Darshan Mehta

30.9k14 gold badges76 silver badges112 bronze badges

Comments

Jenish · Accepted Answer · 2017-04-24 09:41:28Z

1

you can get result using this

SELECT SUM(qty) As qty, (SELECT Price FROM table price desc limit 1) AS price FROM table price dlimit 1

edited Apr 24, 2017 at 9:41

answered Apr 24, 2017 at 9:33

Jenish

5334 silver badges16 bronze badges

Collectives™ on Stack Overflow

CI How to select the last row of another column in a select sum of another column query

3 Answers 3

2 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Related