Perform calculation from count of values for a distinct value

Question

My Goal is to show the top hosts with successful access attempts (Total, %)

• 200 = successful attempt
• 404 = unsuccessful attempt

logs table

host | status 10.1.1.1 | 200 10.1.1.1 | 200 10.1.1.1 | 404 10.10.10.1 | 200 10.10.10.1 | 200 10.10.10.1 | 404

SELECT DISTINCT(host) AS Host, count(host) as Attempts
FROM logs
GROUP BY host
ORDER BY count(status) DESC;

Results in

Host       | Attempts
10.1.1.1   |    3
10.10.10.1 |    3

Query

SELECT DISTINCT(host) as Host, count(status) as Failed
FROM logs
WHERE status = '404'
GROUP BY host
ORDER BY count(host) DESC;

Returns

Host       | Failed
10.1.1.1   |    1
10.10.10.1 |    1

so far so good for getting started, now however when I try this query

SELECT DISTINCT(host) as Host,
    (SELECT count(status)
     FROM logs
     WHERE status = '404') AS Failed,
     (SELECT count(status)
     FROM logs
     WHERE status = '200') AS Successful
FROM logs
GROUP BY host
ORDER BY count(host) DESC;

it returns

Host       | Failed |Successful
10.1.1.1   |    2   |   4
10.10.10.1 |    2   |   4

I'm trying to get it to return 1 failure and 2 successful. I can see what it's doing (returning the total for all hosts). Eventually I'm trying to get to where I divide the total of the successful attempts (2) by total count(status) (3) and return a percentage. I have searched for hours on this one problem with no success, any help would be really appreciated.

Answer 1

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

Answer 2

The expression is more like this:

    SUM(status = 404) / COUNT(*)

In the SUM, put a boolean expression, noting that TRUE computes as 1 and FALSE computes as 0.