CODEWITHSUNDEEP
phpmysqlcodeigniter
Charas
Charas

Reputation: 1831

How do I get 2 sums on 2 conditions in 1 SQL query?

I have a table as illustrated below. How do I SELECT SUM column price WHERE type=1 AND type=2? I want the total of the price where type=1 and the total of the price where type=2 in just one query.

+-----+------+-------+
| PID | Type | Price |
+-----+------+-------+
|   1 |    1 |   100 |
|   2 |    2 |    65 |
|   3 |    1 |   300 |
|   4 |    1 |   200 |
|   5 |    2 |    50 |
+-----+------+-------+

I have tried this but it returns an error:

$this->db->select("SUM(price) as total1, (SELECT SUM(price) where type=2) as total2");
$this->db->from('table');
$this->db->where('type', '1');
$query = $this->db->get();
$result = $query->result();

I have also tried a few other ways of writing my query but to no avail, and all other ways I found in SO are too complicated and too much for my simple query. I think this should be a very simple query.

Upvotes: 0

Views: 456

Answers (5)

Sloan Thrasher
Sloan Thrasher

Reputation: 5040

This will give you your two totals:

SELECT
    SUM(IF(type=1,price,0)) AS Total1,
    SUM(IF(type=2,price,0)) AS Total2
FROM table
WHERE type in (1,2)

Upvotes: 0

Janath Vimukthi Don Francisco
Janath Vimukthi Don Francisco

Reputation: 79

$sql=select price from table where type=1";
$exesql=mysqli_query($sql) or die(mysqli_error());
while($row=mysql_fetch_array($exesql)){
$tpeOneprice=$tpeOneprice+$row['price'];


}
echo $tpeOneprice;

do this to the type 2 also just like this.

Upvotes: 3

Touheed Khan
Touheed Khan

Reputation: 2151

In my opinion group by is best option(options 2) for your requirement.

Option 1 :

$query = $this->db->query("select SUM(CASE WHEN type= '1' then 1 else 0 end) as total1, SUM(CASE WHEN type= '2' then 1 else 0 end) as total2 from table");
$result = $query->result();

PS: You can also use group by on type column.

With Group By (Option 2) :

$this->db->select("SUM(type)");
$this->db->from('table');
$this->db->group_by('type');
$query = $this->db->get();
$result = $query->result();

Upvotes: 0

Khushboo Mahajan
Khushboo Mahajan

Reputation: 13

I just tried in mysql.

Try this:

SELECT type, SUM(price) FROM test GROUP BY type

Upvotes: 0

Rahul Sharma
Rahul Sharma

Reputation: 622

Try this:

$this->db->select("SUM(price) as total1, (SELECT SUM(price) from table where type=2) as total2");
$this->db->from('table');
$this->db->where('type', '1');
$query = $this->db->get();
$result = $query->result();

Upvotes: 1

Related Questions