SQLize | PHPize | SQLtest

CREATE TABLE `keywords` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `profession` varchar(255) NOT NULL, `region` varchar(255) NOT NULL, `rating` int(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `keywords_rating_index` (`name`,`region`,`profession`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `keywords` (`name`, `profession`, `region`, `rating`) VALUES ("git", "front-end developer", "москва", 10), ("git", "дизайнер", "москва", 3), ("git", "backend developer", "москва", 12), ("git", "backend developer", "новосибирск", 7), ("MySql", "backend developer", "новосибирск", 7), ("MySql", "аналитик", "новосибирск", 5); CREATE TABLE `salaries` ( `id` int(11) NOT NULL AUTO_INCREMENT, `keywordId` int(11) NOT NULL, `salary` int(11) NOT NULL, `freq` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `fk_keywordId` (`keywordId`), CONSTRAINT `fk_keywordId` FOREIGN KEY (`keywordId`) REFERENCES `keywords` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `salaries` (`keywordId`, `salary`, `freq`) VALUES (1, 100000, 3), (1, 150000, 2), (1, 200000, 1), (2, 100000, 5), (2, 130000, 3), (2, 150000, 7), (3, 100000, 1), (3, 150000, 7), (3, 170000, 4), (4, 100000, 5), (4, 125000, 5), (4, 150000, 2), (5, 100000, 8), (5, 150000, 1); with t as (select salary, name, count(*) as count from salaries join keywords on keywords.id = keywordId group by salary, name order by name, salary desc) select name, json_arrayagg(JSON_OBJECT(salary, (select sum(count) from t as t1 where t.salary = t1.salary and t1.name = t.name))) as info from t group by name; select salary, JSON_OBJECT(name, count(*)) as count from salaries join keywords on keywords.id = keywordId group by salary, name order by name, salary;