mysql subtract in select
mysql, sql
Solution
If there's no matching B, a left join will return a null, meaning you're doing
numall - NULL
which results in a NULL. To get around this, you'd have to do
numall - COALESCE(numout, 0)
to force a 0 for the non-existent 'b' dates.
Problem
For example, I have the following table: ``` date | id | num 01-01 | a | 10 01-02 | a | 14 01-02 | b | 2 01-03 | a | 19 01-03 | b | 5 01-04 | a | 13 ``` I want to substract `num` of `b` from `a`, that is, the result will be: ``` 01-01 | 10 //10 01-02 | 12 //14-2 01-03 | 14 //19-5 01-04 | 13 //13 ``` I tried the following SQL query but if there is no record of `b` on a `date`, it will return `\N`. ``` SELECT tba.date, numall-numout FROM ( SELECT date, num AS numall FROM tb WHERE id = "a" ) tba LEFT JOIN ( SELECT date, num AS numout FROM tb WHERE id = "b" ) tbb ON tba.date = tbb.date ```