To select by condition, and the fact that was not found to specify as zero?

Good evening!
Correct the essence of the issue, if not the right way to put it.

How can one query to get a list of records that would display all records but the number was only one record that fits the condition?

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 bring the other colors, only with total = 0, respectively.
How it can be implemented?
April 3rd 20 at 18:56
1 answer
April 3rd 20 at 18:58
Solution
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;
Thank you. Something that is necessary.
Only adjusted to the realities of - jeffery_Cartwrig commented on April 3rd 20 at 19:01

Find more questions by tags MySQLSQL