Basj Basj - 2 years ago 65
SQL Question

Count number of visits except the ones from 2 biggest visitors with SQL

I have a website visits database table:

date ip
2016/05/12 08:12
2016/05/12 08:13
2016/05/12 08:20
2016/05/12 08:21
2016/05/12 08:22
2016/05/12 08:40

I count the number of visits with:

select count(ip) from visits where date(date) = date(...)

Now I want to count the number of visits made by everyone except the two IP who have the most visits.

Example: here the IP with most visits are and, and thus should not be counted. The result should be = 1 here.

How to count this with SQL?

Answer Source

You need to exclude from count the rows where ip is equal to the ones that have the maximum number of rows. It is a typical subquery case.

Try this:

     FROM visits
    WHERE ip NOT IN (
                  SELECT ip
                    FROM vistis
                GROUP BY ip
                ORDER BY count(ip) DESC 
                   LIMIT 2
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download