Maintain multiple row data in single row mysql

Question

There is my table look like

id	name	deduction	amount
01	teat	Home Rent	1000
01	test	GPF	500

i want show my data in deduction report like below table

id	name	home_rent	gpf
01	teat	1000	500

mysql code

  SELECT a.* , a.amount as home_rent ,b.amount as gpf FROM my_table as a ,my_table as b where a.deduction = Home Rent and b.deduction = GPF 

what i have done wrong please let me know ? what can i do for my report to it look like my second table thank you ...

Answer 1

We can use conditional aggregation and pivoting here:

SELECT
    id,
    name,
    MAX(CASE WHEN deduction = 'Home Rent' THEN amount END) AS home_rent,
    MAX(CASE WHEN deduction = 'GPF'       THEN amount END) AS gpf
FROM my_table
GROUP BY 1, 2;

Answer 2

use conditional aggregation

Schema (MySQL v5.7)

CREATE TABLE my_table (
  `id` INTEGER,
  `name` VARCHAR(4),
  `deduction` VARCHAR(9),
  `amount` INTEGER
);

INSERT INTO my_table
  (`id`, `name`, `deduction`, `amount`)
VALUES
  ('01', 'test', 'Home Rent', 1000),
  ('01', 'test', 'GPF', 500);

---

Query #1

SELECT 
    id,
    name,
    SUM(CASE WHEN deduction = 'Home Rent' THEN amount ELSE 0 END) AS home_rent,
    SUM(CASE WHEN deduction = 'GPF' THEN amount ELSE 0 END) AS gpt    
FROM my_table
GROUP BY 1, 2;

id	name	home_rent	gpt
1	test	1000	500

---

View on DB Fiddle