SQLize Online / PHPize Online  /  SQLtest Online

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); select name, sum(rating), group_concat(salary order by salary desc) from keywords join salaries on salaries.keywordId = keywords.id where profession="backend developer" group by name order by sum(rating) DESC limit 10;