mysql get difference of two sum on the same table

Question

how can i get the result in just one query instead of this one:

SELECT SUM(`quantity`) as type0 FROM `fruits_delivery` 
    WHERE `fid`='1001' AND `type`=0;

result_1 = type0 ;

SELECT SUM(`quantity`) as type1 FROM `fruits_delivery`  
    WHERE `fid`='1001' AND `type`=1;

result_2 = type1 ;

final_result = result_1 - result_2;

Solution 1

You should use this

SELECT sum(IF(`type`=0, `quantity`, 0))-sum(IF(`type`=1, `quantity`, 0)) 
        AS    `final_result` 
        FROM   `fruits_delivery` 
        WHERE  `fid` = '1001' 
               

sqlfiddle

---

Old Answer

SELECT T1.result - T2.result AS `final_result` 
FROM   (SELECT Sum(`quantity`) AS result, 
               `fid` 
        FROM   `fruits_delivery` 
        WHERE  `fid` = '1001' 
               AND `type` = 0 
        LIMIT  1) AS T1 
       JOIN (SELECT Sum(`quantity`) AS result, 
                    `fid` 
             FROM   `fruits_delivery` 
             WHERE  `fid` = '1001' 
                    AND `type` = 1 
             LIMIT  1) AS T2 
         ON ( T1.fid = T2.fid ) 

SQLFiddle

Solution 2

Alternatively, you can also do it using CASE

SELECT  SUM(CASE WHEN type = 0 THEN quantity ELSE 0 END) -
        SUM(CASE WHEN type = 1 THEN quantity ELSE 0 END)
          AS final_result
FROM    fruits_delivery
WHERE   fid = '1001'

• SQLFiddle Demo

Comments

[alisongaleon]

hi, thanks for the reply but i get the #1054 - Unknown column 'T1.result' in 'field list' error..

[Shiplu Mokaddim]

@alisongaleon, Yeah. the SQL formatter changed it. I have updated. Try again

[John Woo]

this can be simply done using CASE, not by using two subqueries and joining them.

[Shiplu Mokaddim]

Thanks @JW. I have added an IF version which is even shorter and appropriated.

[Pedigree]

+1. for answering the shorter version first before the update made by the accepted answer!