Select by condition, but what is not found should be specified as zero?

Question

MySQL

Good evening!
Please correct the question if it is not correct.
How can one query get a list of records so that all records are displayed, but only the record that matches the condition has a count?

select c.color_id, c.color_name, count(p.product_id) as total
from color as c
left join product as p on c.color_id = p.color_id
where p.color_id = 2
group by c.color_id

In this example, the result will be

{
    color_id = 2
    color_name = red
    total = 47
}

But I would like to display the rest of the colors, only with total = 0, respectively.
How can this be implemented?

Answer 1

https://www.db-fiddle.com/f/emqonUSGZKx36N7jNVN1Ew/2

select c.color_id, c.color_name,
       count(p.product_id) as total,
       count(case when c.color_name = 'red' then 1 end) as red_cnt
from color as c
         left join product as p on c.color_id = p.color_id
group by c.color_id, c.color_name;