Sum and Display MySQL to PHP Table

Question

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

What residual is ?

user6063698
– user6063698

2016-09-02 16:51:56 +00:00
Commented Sep 2, 2016 at 16:51 — user6063698
– user6063698, Commented Sep 2, 2016 at 16:51
residual is mean from nominal - DB (debit)..

Ferdian
– Ferdian

2016-09-02 17:37:13 +00:00
Commented Sep 2, 2016 at 17:37 — Ferdian
– Ferdian, Commented Sep 2, 2016 at 17:37

Strawberry · Accepted Answer · 2016-09-02 17:30:46Z

2

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.

edited Sep 2, 2016 at 17:30

Strawberry

34k14 gold badges43 silver badges57 bronze badges

answered Sep 2, 2016 at 16:46

devlin carnate

8,6617 gold badges55 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Strawberry Over a year ago

The fifth line is a little bit strange, but otherwise fine.

Ferdian Over a year ago

Excelent, you've save my rest tonight.. Your syntax are actually correct.. :)

Ferdian Over a year ago

Done :) I'm sorry, because i'm newbie here.. I need guidance from all.. Thanks again @devlincarnate

Collectives™ on Stack Overflow

Sum and Display MySQL to PHP Table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related