If you want the result to be in one row you can use:
SELECT
SUM(IF(color="YELLOW", 1, 0)) AS YELLOW,
SUM(IF(color="BLUE", 1, 0)) AS BLUE,
SUM(IF(color="RED", 1, 0)) AS RED
FROM t_table
Working example
If you want the result to be in one row you can use:
SELECT
SUM(IF(color="YELLOW", 1, 0)) AS YELLOW,
SUM(IF(color="BLUE", 1, 0)) AS BLUE,
SUM(IF(color="RED", 1, 0)) AS RED
FROM t_table
Working example