Basj Basj - 5 months ago 6
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?


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