Perform calculation from count of values for a distinct value
First, distinct is not a function, it applies to the whole row. Most straight forward way would be:

Select host, status, count(1)
From logs
Where status in (200,404)
Group by host, status


This will however return one row for each status. My suggestion is to do what remains in the application since that is all about presentation. If you dont want to do that you can use a case expression to transpose the result:

Select host
    , count(case when status = 200 then 1 end) as #success
    , count(case when status = 404 then 1 end) as #failure
From logs
Where status in (200,404)
Group by host


Sorry about the formatting, typing from my mobile