Multiple COUNT() for multiple conditions in one query (MySQL)

SELECT color, COUNT(*) FROM t_table GROUP BY color

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