Mysql:Find employee with same salary in employee table

 

Find employee with same salary from employee table

CREATE TABLE employee (

    id INT AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(100) NOT NULL,

    age INT,

    city VARCHAR(100),

    salary DECIMAL(10, 2),

    doj DATE

);

INSERT INTO employee (name, age, city, salary, doj)

VALUES

('John', 28, 'Noida', 50000, '2022-06-15'),

('Jerry', 32, 'Delhi', 65000, '2020-03-10'),

('Bill', 25, 'Mumbai', 45000, '2023-01-20'),

('Siara', 30, 'Pune', 70000, '2019-08-05');

INSERT INTO employee (name, age, city, salary, doj)

VALUES

('Amit', 29, 'Delhi', 50000, '2021-04-12'),

('Rahul', 35, 'Noida', 65000, '2018-07-19'),

('Neha', 27, 'Mumbai', 45000, '2022-11-03'),

('Priya', 31, 'Pune', 70000, '2020-02-14'),

('Raj', 26, 'Delhi', 50000, '2023-05-22'),

('Ankit', 33, 'Noida', 75000, '2017-09-11'),

('Sneha', 28, 'Mumbai', 65000, '2021-12-01'),

('Vikas', 37, 'Pune', 80000, '2016-06-18'),

('Pooja', 29, 'Delhi', 75000, '2022-03-25'),

('Karan', 34, 'Noida', 80000, '2019-10-07');

Solution

SELECT salary,JSON_ARRAYAGG(name) AS employee_salary

FROM employee group by salary;

Output:

+----------+-----------------------------+

| salary   | employee_salary             |

+----------+-----------------------------+

| 45000.00 | ["Bill", "Neha"]            |

| 50000.00 | ["John", "Amit", "Raj"]     |

| 65000.00 | ["Jerry", "Rahul", "Sneha"] |

| 70000.00 | ["Siara", "Priya"]          |

| 75000.00 | ["Ankit", "Pooja"]          |

| 80000.00 | ["Vikas", "Karan"]          |

+----------+-----------------------------+

6 rows in set (0.00 sec)

SELECT salary,group_concat(name) AS employee_salary

FROM employee group by salary;

Output:

+----------+-------------------+

| salary   | employee_salary   |

+----------+-------------------+

| 45000.00 | Bill,Neha         |

| 50000.00 | John,Amit,Raj     |

| 65000.00 | Jerry,Rahul,Sneha |

| 70000.00 | Siara,Priya       |

| 75000.00 | Ankit,Pooja       |

| 80000.00 | Vikas,Karan       |

+----------+-------------------+

6 rows in set (0.00 sec)

SELECT salary, JSON_OBJECTAGG(name, city)  FROM employee GROUP BY salary;

Output:

+----------+---------------------------------------------------------+

| salary   | JSON_OBJECTAGG(name, city)                              |

+----------+---------------------------------------------------------+

| 45000.00 | {"Bill": "Mumbai", "Neha": "Mumbai"}                    |

| 50000.00 | {"Raj": "Delhi", "Amit": "Delhi", "John": "Noida"}      |

| 65000.00 | {"Jerry": "Delhi", "Rahul": "Noida", "Sneha": "Mumbai"} |

| 70000.00 | {"Priya": "Pune", "Siara": "Pune"}                      |

| 75000.00 | {"Ankit": "Noida", "Pooja": "Delhi"}                    |

| 80000.00 | {"Karan": "Noida", "Vikas": "Pune"}                     |

+----------+---------------------------------------------------------+