Jump to content

subtraction from tables


chambers9661

Recommended Posts

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

 

Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

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

 

  • Like 1
Link to comment
Share on other sites

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 ?

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.