CODEWITHSUNDEEP
mysql
Ferdian
Ferdian

Reputation: 76

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)

Upvotes: 0

Views: 54

Answers (1)

devlin carnate
devlin carnate

Reputation: 8592

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.

Upvotes: 2

Related Questions