Question 20858 in Mysql Question posted in
The official MySQL documentation can be found here.

Maintain multiple row data in single row mysql

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 …

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search