Perform SUM and SUBTRACTION on the basis of a condition in a single MySQL query?

For this, use CASE statement and set for both SUM and SUBTRACTION. Let us first create a table −

mysql> create table DemoTable866(    Status varchar(100),    Amount int ); Query OK, 0 rows affected (0.63 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable866 values('ACTIVE',50); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable866 values('INACTIVE',70); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable866 values('INACTIVE',20); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable866 values('ACTIVE',100); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable866 values('ACTIVE',200); Query OK, 1 row affected (0.11 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable866;

This will produce the following output −

+----------+--------+ | Status   | Amount | +----------+--------+ | ACTIVE   | 50    | | INACTIVE | 70     | | INACTIVE | 20     | | ACTIVE   | 100    | | ACTIVE   | 200    | +----------+--------+ 5 rows in set (0.00 sec)

Following is the query to perform SUM or SUBTRACTION in the same column with a single MySQL query −

mysql> select sum(CASE WHEN Status = 'ACTIVE' then Amount    WHEN Status = 'INACTIVE' then -Amount    END) AS RemainingAmount    from DemoTable866;

This will produce the following output −

+-----------------+ | RemainingAmount | +-----------------+ | 260             | +-----------------+ 1 row in set (0.06 sec)