subtraction from tables

[chambers9661]

Hi 

I have a database in the database is a table called incomeuser1 there is a column called AMOUNT.

Theres is also another table called expenseuser1 and a column called EXPAMOUNT.

What code do i use to subtract one from other. For example if incomeuser1 AMOUNT was say 50.00. And expenseuser1 EXPAMOUNT was say 35.50 how do i subtract and echo the result as 14.50 using 2 decimal places ?

Also if the sum was a minus figure how would i display that in red?

Novice learner

 

[Barand]

You would use a query with a JOIN between the two tables to get the result

SELECT FORMAT(i.amount - e.expamount, 2) as diff
FROM incomeuser1 i
     JOIN
     expenseuser1 e ON i.whatever = e.whatever

In your php code, apply a different css class if the diff value is -ve

 

edit; When I see tables with a number suffix in their name I have doubts about the database design.

Edited June 15, 2020 by Barand

[Barand]

Alternatively, define your monetary amount columns as decimal(). This gives fixed number of decimal places. Here's an example

DATA

CREATE TABLE `income` (                                        CREATE TABLE `expense` (
  `income_id` int(11) NOT NULL AUTO_INCREMENT,                   `expense_id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) DEFAULT NULL,                                 `userid` int(11) DEFAULT NULL,
  `pay_date` date DEFAULT NULL,                                  `expend_date` date DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,                           `expamount` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`income_id`),                                     PRIMARY KEY (`expense_id`),
  KEY `idx_income_userid` (`userid`)                             KEY `idx_expense_userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;                          ) ENGINE=InnoDB DEFAULT CHARSET=utf8;


income                                                         expense
+-----------+--------+------------+---------+                  +------------+--------+-------------+-----------+
| income_id | userid | pay_date   | amount  |                  | expense_id | userid | expend_date | expamount |
+-----------+--------+------------+---------+                  +------------+--------+-------------+-----------+
|         1 |      1 | 2020-01-01 | 2500.00 |                  |          1 |      1 | 2020-01-15  |    800.00 |
|         2 |      1 | 2020-02-01 | 2650.00 |                  |          2 |      1 | 2020-01-25  |    250.00 |
|         3 |      1 | 2020-03-01 | 2400.00 |                  |          3 |      2 | 2020-01-21  |   1500.00 |
|         4 |      2 | 2020-01-01 | 3000.00 |                  |          4 |      2 | 2020-02-10  |    500.00 |
|         5 |      2 | 2020-02-01 | 3100.00 |                  |          5 |      2 | 2020-03-15  |   1800.00 |
|         6 |      2 | 2020-03-01 | 2800.00 |                  |          6 |      2 | 2020-03-20  |   1600.00 |
+-----------+--------+------------+---------+                  +------------+--------+-------------+-----------+

QUERY

SELECT i.userid
     , mname
     , income
     , expense
     , income - expense as diff
FROM (
        SELECT userid
             , EXTRACT(YEAR_MONTH from pay_date) as month
             , MONTHNAME(pay_date) as mname
             , SUM(amount) as income
        FROM income
        GROUP BY userid, month
     ) i
      
     LEFT JOIN
     
     (
        SELECT userid
             , EXTRACT(YEAR_MONTH from expend_date) as month
             , SUM(expamount) as expense
        FROM expense
        GROUP BY userid, month
     ) e USING (userid, month);
     
+--------+----------+---------+---------+---------+
| userid | mname    | income  | expense | diff    |
+--------+----------+---------+---------+---------+
|      1 | January  | 2500.00 | 1050.00 | 1450.00 |
|      1 | February | 2650.00 |    NULL |    NULL |
|      1 | March    | 2400.00 |    NULL |    NULL |
|      2 | January  | 3000.00 | 1500.00 | 1500.00 |
|      2 | February | 3100.00 |  500.00 | 2600.00 |
|      2 | March    | 2800.00 | 3400.00 | -600.00 |
+--------+----------+---------+---------+---------+

 

[chambers9661]

Wow thanks

[chambers9661]

22 hours ago, Barand said:

You would use a query with a JOIN between the two tables to get the result

SELECT FORMAT(i.amount - e.expamount, 2) as diff
FROM incomeuser1 i
     JOIN
     expenseuser1 e ON i.whatever = e.whatever

In your php code, apply a different css class if the diff value is -ve

 

edit; When I see tables with a number suffix in their name I have doubts about the database design.

How do i echo this ?

[Barand]

4 hours ago, chambers9661 said:

How do i echo this ?

That would depend on the code get the query results