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.