CODEWITHSUNDEEP
phpmysqlsql
Sangam Jung Gauli
Sangam Jung Gauli

Reputation: 143

php sql query statement for total

I am trying to get total from a stock table from my database.

I have data as follows:

Name     Quantity    Type
A        12          add
B        10          add
A        3           sub
B        4           sub

Now I want to calculate total quantity as add refers addition and sub refers subtraction. Output must be like:

Name     Quantity    
A        9         
B        6

I have used following:

        $this->db->select('stock.Name,sum() quantity as tot');
        $this->db->from('stock');
        $this->db->group_by('stock.Name');
        $query = $this->db->get();
        return $query->result();

Upvotes: 0

Views: 31

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133360

select name, sum( case when type = 'add' then quantity else -quantity end) as total 
from my_table 
group by name

Upvotes: 4

Gurwinder Singh
Gurwinder Singh

Reputation: 39467

You can use a CASE to determine if the quantity needs to be added or subtracted:

select name,
    sum(case 
            when type = 'add'
                then quantity
            when type = 'sub'
                then - quantity
            end) quantity
from your_table
group by name;

Note that this will not honor any operation other than add and sub, which should be a desired case.

Upvotes: 4

Related Questions