MySQL – Using Multiple Selects on a Single Table

MySQLpivot

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.

Best Answer

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 |
+-----------------+---------+---------+---------+

Related Solutions

Mysql – select based on sum of rows compared to another field value

This is an UNPIVOT problem.To get the sum of credit items and the sum of debit items you can use a SUM of a CASE statement. To show that, we need a table first:

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE invoice_items
    (`invoice_item_id` int, `invoice_set_id` varchar(3), `credit_debit` varchar(6), `amount` int)
;

INSERT INTO invoice_items
    (`invoice_item_id`, `invoice_set_id`, `credit_debit`, `amount`)
VALUES
    (62, 'a22', 'debit', 15.00),
    (63, 'a22', 'debit', 8.00),
    (64, 'a22', 'credit', 23.00),
    (65, 'b23', 'debit', 44.00),
    (66, 'c55', 'debit', 15.00),
    (67, 'c55', 'debit', 2.00),
    (67, 'c55', 'credit', 8.00)
;

Now you can combine SUM and CASE like this: Query 1:

SELECT invoice_set_id,
       SUM(CASE WHEN credit_debit = 'debit' THEN amount END) AS debit_amount,
       SUM(CASE WHEN credit_debit = 'credit' THEN amount END) AS credit_amount
  FROM invoice_items
 GROUP BY invoice_set_id;

Results:

| INVOICE_SET_ID | DEBIT_AMOUNT | CREDIT_AMOUNT |
|----------------|--------------|---------------|
|            a22 |           23 |            23 |
|            b23 |           44 |        (null) |
|            c55 |           17 |             8 |

From here it is fairly simple to filter out the unwanted rows. We just need to wrap the two sums in COALESCE statements to turn NULL into 0:

Query 2:

SELECT invoice_set_id
  FROM(
    SELECT invoice_set_id,
           SUM(CASE WHEN credit_debit = 'debit' THEN amount END) AS debit_amount,
           SUM(CASE WHEN credit_debit = 'credit' THEN amount END) AS credit_amount
      FROM invoice_items
     GROUP BY invoice_set_id
  )X
WHERE COALESCE(debit_amount,0) > COALESCE(credit_amount,0);

Results:

| INVOICE_SET_ID |
|----------------|
|            b23 |
|            c55 |

If your actual problem is as simple as your example and you do not need the intermediate values for anything else, you can get the result also by just changing the sign of the amount based on the credit_debit value before calculating the sum. That would look like this:

Query 3:

SELECT invoice_set_id
  FROM invoice_items
 GROUP BY invoice_set_id
HAVING SUM(CASE WHEN credit_debit = 'debit' THEN amount ELSE -amount END)>0;

Results:

| INVOICE_SET_ID |
|----------------|
|            b23 |
|            c55 |

MySQL Pivot Table – Convert Rows to Columns

Perhaps this is closer to what you want? It not, see if it gives you a clue about how to "pivot".

SELECT  
        SUM(if(ID = 1, locationID, 0)) Location_Id_1,          
        SUM(if(ID = 2, locationID, 0)) Location_Id_2,          
        SUM(if(ID = 3, locationID, 0)) Location_Id_3,          
        SUM(if(ID = 4, locationID, 0)) Location_Id_4,          
        SUM(if(ID = 5, locationID, 0)) Location_Id_5
    FROM boxlocations;

(MAX() should give the same result as SUM().)

Best Answer

Related Solutions

Mysql – select based on sum of rows compared to another field value

MySQL Pivot Table – Convert Rows to Columns

Related Question