Ferdian Ferdian - 3 months ago 9
MySQL Question

Sum and Display MySQL to PHP Table

+----+--------+------+------------+-----------+----------+------+
| id | deb_id | name | date | nominal | duration | type |
+----+--------+------+------------+-----------+----------+------+
| 1 | K7PJ8 | John | 2016-09-04 | 100000.00 | 2 | DB |
| 2 | K7PJ7 | Rey | 2016-08-30 | 125000.00 | 1 | DB |
| 3 | K7PJ8 | John | 2016-09-05 | 50000.00 | 2 | CR |
| 4 | K7PJ7 | Rey | 2016-08-05 | 25000.00 | 1 | CR |
+----+--------+------+------------+-----------+----------+------+


From the MySQL table above, it is possible if I want to display like the table below using PHP, and what syntax can I use for this ?

Result :

+--------+------+------------+------------+
| deb_id | name | nominal | residual |
+--------+------+------------+------------+
| K7PJ8 | John | 100000.00 | 50000.00 |
| K7PJ7 | Rey | 125000.00 | 100000.00 |
+--------+------+------------+------------+


Grouped by deb_id, and then sum the nominal WHERE type =(CR-DB)

Answer
SELECT deb_id
     , name
     , SUM(CASE WHEN type = 'DB' THEN nominal ELSE 0 END) AS nominal
     , SUM(CASE WHEN type = 'CR' THEN nominal ELSE 0 END) AS credit
     , SUM(CASE WHEN type = 'DB' THEN nominal ELSE 0 END) - SUM(CASE WHEN type = 'CR' THEN nominal ELSE 0 END) AS residual
  FROM myTable
 GROUP 
    BY deb_id
     , name

You can you a case statement to separate those values into two separate columns.

Comments