using multiple selects on a single table mysql

Question

I have a table like this in my database

now what I want is to extract the sum of credit where credit_type equals V and sum of credit where credit_type equals R, then find the difference between the two(R and V). My result should contain identity_number, sum of credit where credit_type equals V, sum of credit where credit_type equals R, balance between the two sums. As an example looking at the above table identity_number 2885 should have something like this in my returned result 2885, 3000, 1560+370, 3000-(1560+370). How do I write such a query? hope you understood the problem.

Answer 1

You may use SUM function with IF to choose what to take into consideration for each row. Here is my example:

mysql> select * from credit;
+------+-----------------+--------+-------------+
| id   | identity_number | credit | credit_type |
+------+-----------------+--------+-------------+
|    1 |            2067 |   2000 | V           |
|    2 |            2885 |   1560 | R           |
|    3 |            2390 |    800 | V           |
|    4 |            3452 |      0 | R           |
|    5 |            2885 |   3000 | V           |
|    6 |            2885 |    370 | R           |
+------+-----------------+--------+-------------+


mysql> select identity_number, sum(if(credit_type='V', credit, 0)) as v_total, sum(if(credit_type='R', credit, 0)) as R_total from credit group by identity_number;
+-----------------+---------+---------+
| identity_number | v_total | R_total |
+-----------------+---------+---------+
|            2067 |    2000 |       0 |
|            2390 |     800 |       0 |
|            2885 |    3000 |    1930 |
|            3452 |       0 |       0 |
+-----------------+---------+---------+


mysql> select identity_number, v_total, R_total, (v_total-R_total) as balance from (select identity_number, sum(if(credit_type='V', credit, 0)) as v_total, sum(if(credit_type='R', credit, 0)) as R_total from credit group by identity_number)x;
+-----------------+---------+---------+---------+
| identity_number | v_total | R_total | balance |
+-----------------+---------+---------+---------+
|            2067 |    2000 |       0 |    2000 |
|            2390 |     800 |       0 |     800 |
|            2885 |    3000 |    1930 |    1070 |
|            3452 |       0 |       0 |       0 |
+-----------------+---------+---------+---------+