Grouping values from different rows to a single row and selecting SUM and Difference of values in MY-CodePudding

I am having currently having the following output from the MySQL query.

 ---------- -------------- ------------- ------------------- ------------------ 
| NAME     | ALLO_DETAILS | ALLO_AMOUNT | DEDUCTION_DETAILS | DEDUCTION_AMOUNT |
 ---------- -------------- ------------- ------------------- ------------------ 
| ALI      | basic salary |        1000 | retirement        |              500 |
| ASAD     | basic salary |        1000 | bank a            |              400 |
| JAMSHAID | basic salary |        1000 | bank a            |              400 |
| JAMSHAID | hours        |        1400 | tax               |              200 |
 ---------- -------------- ------------- ------------------- ------------------

the query I have used is:

SELECT `EMPLOYEES`.`EMP_NAME` AS NAME,
 `ALLOCATION`.`ALLO_DETAILS` AS ALLO_DETAILS,
 `ALLOCATION`.`ALLO_AMOUNT` AS ALLO_AMOUNT,
 DEDUCATION.DED_DETAILS AS DEDUCTION_DETAILS,
 DEDUCATION.DED_AMOUNT AS DEDUCTION_AMOUNT
 FROM `EMPLOYEES`,
 `ALLOCATION`,
 `SALARY`,DEDUCATION
 WHERE 
  EMPLOYEES`.`EMP_ID` = `SALARY`.`EMP_ID` 
  AND `ALLOCATION`.`ALLO_ID` = `SALARY`.`ALLO_ID` 
  AND DEDUCATION.DED_ID = `SALARY`.`DED_ID`;

Here are my tables definitions:

CREATE TABLE `EMPLOYEES` (
  `EMP_ID` int DEFAULT NULL,
  `EMP_NAME` varchar(50) DEFAULT NULL
)
CREATE TABLE `DEDUCATION` (
  `DED_ID` int DEFAULT NULL,
  `DED_DETAILS` varchar(100) DEFAULT NULL,
  `DED_AMOUNT` int DEFAULT NULL
)


CREATE TABLE `SALARY` (
  `SAL_ID` int DEFAULT NULL,
  `EMP_ID` int DEFAULT NULL,
  `ALLO_ID` int DEFAULT NULL,
  `DED_ID` int DEFAULT NULL
)

What I am trying to do?
I am trying to get the output like this table:

What I am not getting is that how would I merge the results from different rows to a single row as shown in the above table?
Please ignore the n-columns. I am having the following columns

for total_allocation :

Basic Salary
Earnings
Extra Hours
Dangerous Work

And for total_deduction.

Bank A
Tax
Retirement

CodePudding user response：

This is a sample query to GROUP BY employee and select multiple columns based on known values, so that multiple rows are combined into multiple columns.

SELECT 
  MAX(EMP_NAME),
  SUM(CASE WHEN c.ALLO_DETAILS = 'Basic Salary' THEN c.ALLO_AMOUNT ELSE 0 END) "Basic Salary",
  SUM(CASE WHEN c.ALLO_DETAILS = 'Earnings' THEN c.ALLO_AMOUNT ELSE 0 END) "Earnings",
  SUM(CASE WHEN c.ALLO_DETAILS = 'Extra Hours' THEN c.ALLO_AMOUNT ELSE 0 END) "Extra Hours",
  SUM(CASE WHEN c.ALLO_DETAILS = 'Dangerous Work' THEN c.ALLO_AMOUNT ELSE 0 END) "Dangerous Work",
  SUM(COALESCE(c.ALLO_AMOUNT, 0)) "Total Allocation",
  SUM(CASE WHEN d.DED_DETAILS = 'Bank A' THEN d.DED_AMOUNT ELSE 0 END) "Bank A",
  SUM(CASE WHEN d.DED_DETAILS = 'Tax' THEN d.DED_AMOUNT ELSE 0 END) "Tax",
  SUM(CASE WHEN d.DED_DETAILS = 'Retirement' THEN d.DED_AMOUNT ELSE 0 END) "Retirement",
  SUM(COALESCE(d.DED_AMOUNT, 0)) "Total Deduction",
  SUM(COALESCE(c.ALLO_AMOUNT, 0)) - SUM(COALESCE(d.DED_AMOUNT, 0)) "Salary"
FROM EMPLOYEES a
JOIN SALARY b ON a.EMP_ID = b.EMP_ID
LEFT JOIN ALLOCATION c ON b.ALLO_ID = c.ALLO_ID
LEFT JOIN DEDUCATION d ON b.DED_ID = d.DED_ID
GROUP BY a.EMP_ID

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ea4ef4e2b6f9fe445e09ca2522973a7d