0

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.

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.