# Perform calculation from count of values for a distinct value

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.

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

• You deserve bonus points for answering via mobile, I took your answer and ran with it, Thanks!
– Seez
Commented Dec 7, 2018 at 1:21
``````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`.