Count of distinct values of a table using sql
This is know as conditional counting and can be achieved using a case expression:

select name, 
       count(case when number = 8 then 1 end) as count_of_8,
       count(case when number = 41 then 1 end) as count_of_41
from the_table
group by name
order by name;


Aggregate functions (like count()) ignore NULL values. The case expression inside the count() returns null for those values that we don't want to count, hence only the number of rows containing that value are counted. case when x then y end is equivalent to case when x then y else null end