MySQL query for adding/subtracting values and group by month

Question

I have 3 tables: 2 tables and 1 SQL view. I am not getting right results.

1 table

CREATE TABLE `salary_earning` (
  `id` int NOT NULL AUTO_INCREMENT,
  `basic_salary` int NOT NULL,
  `health_allowance` int NOT NULL,
  `transport_allowance` int NOT NULL,
  `overtime_allowance` int NOT NULL,
  `leave_encashment` int NOT NULL,
  `accomodation_allowance` int NOT NULL,
  `bonus_allowance` int NOT NULL,
  `emp_id` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; 

INSERT INTO `salary_earning` VALUES
(1, 100, 20, 15, 10, 5, 30, 5, 101, '2020-10-10');

INSERT INTO `salary_earning` VALUES
(2, 0, 0, 0, 0, 0, 0, 30, 101, '2020-10-11');

INSERT INTO `salary_earning` VALUES
(3, 100, 20, 15, 10, 5, 30, 5, 102, '2020-11-10');

2 table

CREATE TABLE `salary_deduction` (
 `id` int NOT NULL AUTO_INCREMENT,
 `income_tax` int NOT NULL,
 `advance_money` int NOT NULL,
 `emp_id` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
 `date` date NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `salary_deduction` VALUES
(1, 12, 30, '101', '2020-10-10');

3 view

SELECT 
    slr.*,
    `total_earning` - `total_deduction` AS `final_salary`
FROM (
  SELECT 
    `se`.`emp_id`,
    `se`.`date`,
    (
        `basic_salary`+
        `health_allowance`+
        `transport_allowance`+
        `overtime_allowance`+
        `leave_encashment`+
        `accomodation_allowance`+
        `bonus_allowance`
    ) AS `total_earning`,
    IFNULL(`income_tax` + `advance_money`, 0) AS `total_deduction`
  FROM `salary_earning` `se`
  LEFT JOIN `salary_deduction` `sd` ON 
    `se`.`emp_id` = `sd`.`emp_id` AND `se`.`date` = `sd`.`date`
) slr;

CURRENT OUTPUT

+--------+------------+---------------+-----------------+--------------+
| emp_id |       date | total_earning | total_deduction | final_salary |
+--------+------------+---------------+-----------------+--------------+
|    101 | 2020-10-10 |           185 |              42 |          143 |
|    101 | 2020-10-11 |            30 |               0 |           30 |
|    102 | 2020-11-10 |           185 |               0 |          185 |
+--------+------------+---------------+-----------------+--------------+

DESIRED OUTPUT/WHAT IAM LOOKING FOR

+--------+------------+---------------+-----------------+--------------+
| emp_id |       date | total_earning | total_deduction | final_salary |
+--------+------------+---------------+-----------------+--------------+
|    101 | Oct 20     |           215 |              42 |          173 |
|    102 | Nov 20     |           185 |               0 |          185 |
+--------+------------+---------------+-----------------+--------------+

So, i want an aggregate for a full month if a new value(either under earning or deduction) is added with same emp_id. Can you help me what I am doing wrong.

https://sqlize.online/

score 0 · Answer 1 · answered Oct 21 '20 at 01:23

0

you need to put date format to do that, you can look more here

example:

use %m for get the value number of month. ex : 01 as January
use %M for get the value name of month. ex : January

answered Oct 21 '20 at 01:23

ilham suryoko

55
1
1
9

great thanks...and what about grouping all entries for that month w.r.t emp_id as 1...right now there are 2 entries for a employee 101 for same month – AAA Oct 21 '20 at 02:02
its too complex, need to group by and sum each column, can take look [here](https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by) ref by [Select grouping where all the elements meet the condition ](https://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results) – ilham suryoko Oct 21 '20 at 03:31

score 0 · Answer 2 · answered Nov 13 '20 at 11:05

The solution is pretty simple (SQLize.online):

You just need to aggregate data by formatted date field` like:

SELECT -- select aggregate data
    `slr`.`emp_id`, 
    `slr`.`date`,
    SUM(`total_earning`) `total_earning`,
    SUM(`total_deduction`) `total_deduction`,
    SUM(`total_earning` - `total_deduction`) AS `final_salary`
FROM (
  SELECT 
    `se`.`emp_id`,
    DATE_FORMAT(`se`.`date`, '%M %y') date, -- format date as month - year
    (
        `basic_salary`+
        `health_allowance`+
        `transport_allowance`+
        `overtime_allowance`+
        `leave_encashment`+
        `accomodation_allowance`+
        `bonus_allowance`
    ) AS `total_earning`,
    IFNULL(`income_tax` + `advance_money`, 0) AS `total_deduction`
  FROM `salary_earning` `se`
  LEFT JOIN `salary_deduction` `sd` ON 
    `se`.`emp_id` = `sd`.`emp_id` AND `se`.`date` = `sd`.`date`
) slr
GROUP BY `slr`.`emp_id`, `slr`.`date`; -- group by emp_id and formatted date

MySQL query for adding/subtracting values and group by month

2 Answers2