0

I have a table like this in my database

this is how my table is structured

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.

  • go through group by and union all. you will get the idea. – Spike Sep 15 '16 at 10:22
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 |
+-----------------+---------+---------+---------+
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.