Mysql – Perform calculation from count of values for a distinct value

MySQL

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.

Best Answer

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

Related Question