SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE test_tbl ( u_name varchar NOT NULL, u_color varchar NULL ); INSERT INTO test_tbl (u_name, u_color) values ('Ivanov', 'RED'); INSERT INTO test_tbl (u_name, u_color) values ('Petrov', 'YELLOW'); INSERT INTO test_tbl (u_name, u_color) values ('Ivanov', 'RED'); INSERT INTO test_tbl (u_name, u_color) values ('Petrov', 'YELLOW'); INSERT INTO test_tbl (u_name, u_color) values ('Ivanov', 'GREEN'); INSERT INTO test_tbl (u_name, u_color) values ('Petrov', 'PERFECT'); INSERT INTO test_tbl (u_name, u_color) values ('Petrov', 'RED'); INSERT INTO test_tbl (u_name, u_color) values ('Sidorov', 'GREEN'); INSERT INTO test_tbl (u_name, u_color) values ('Sidorov', 'GREEN'); INSERT INTO test_tbl (u_name, u_color) values ('Sidorov', 'YELLOW'); INSERT INTO test_tbl (u_name, u_color) values ('Ivanov', 'RED'); INSERT INTO test_tbl (u_name, u_color) values ('Sidorov', 'PERFECT'); INSERT INTO test_tbl (u_name, u_color) values ('Sidorov', 'PERFECT'); INSERT INTO test_tbl (u_name, u_color) values ('Petrov', 'YELLOW'); INSERT INTO test_tbl (u_name, u_color) values ('Ivanov', 'GREEN'); INSERT INTO test_tbl (u_name, u_color) values ('Sidorov', 'RED'); INSERT INTO test_tbl (u_name, u_color) values ('Petrov', 'RED'); INSERT INTO test_tbl (u_name, u_color) values ('Petrov', 'GREEN'); INSERT INTO test_tbl (u_name, u_color) values ('Ivanov', 'YELLOW'); INSERT INTO test_tbl (u_name, u_color) values ('Sidorov', 'RED'); -- select * from test_tbl; with RED as (select u_name, count(u_color) as color from test_tbl where u_color = 'RED' group by u_name), YELLOW as (select u_name, count(u_color) as color from test_tbl where u_color = 'YELLOW' group by u_name), GREEN as (select u_name, count(u_color) as color from test_tbl where u_color = 'GREEN' group by u_name), PURPLE as (select u_name, count(u_color) as color from test_tbl where u_color = 'PURPLE' group by u_name) select RED.u_name, coalesce(RED.color,0) as RED, coalesce(YELLOW.color,0) as YELLOW, coalesce(GREEN.color,0) as GREEN, coalesce(PURPLE.color,0) as PURPLE from RED left join YELLOW on RED.u_name = YELLOW.u_name left join GREEN on YELLOW.u_name = GREEN.u_name left join PURPLE on GREEN.u_name = PURPLE.u_name;