Basj Basj - 6 months ago 15
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 26.123.123.2
2016/05/12 08:13 26.123.123.2
2016/05/12 08:20 7.8.13.34
2016/05/12 08:21 1.2.177.9
2016/05/12 08:22 26.123.123.2
2016/05/12 08:40 7.8.13.34


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 26.123.123.2 and 7.8.13.34, and thus should not be counted. The result should be = 1 here.

How to count this with SQL?

Answer

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:

   SELECT COUNT(ip) 
     FROM visits
    WHERE ip NOT IN (
                  SELECT ip
                    FROM vistis
                GROUP BY ip
                ORDER BY count(ip) DESC 
                   LIMIT 2
                )
 GROUP BY ip