Geek asks and answers

Mysql subtract in select

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 subtract numfrom bfrom 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 dateno record b, 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
+3
source share
2 answers

If there is no matching B, the left join will return null, which means you do

numall - NULL

which results in NULL. To get around this, you have to do

numall - COALESCE(numout, 0)

to force 0 for non-existent dates 'b'.

+6
source

Use SELECT tba.date, numall-ISNULL(numout,0)

0
source

More articles:


All Articles