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)