0

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?

Basj
  • 29,668
  • 65
  • 241
  • 451

1 Answers1

4

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
Oscar Pérez
  • 4,119
  • 1
  • 13
  • 31
Giorgos Betsos
  • 68,064
  • 7
  • 48
  • 83
  • Wonderful! If I only want the count (+ date condition), I have `SELECT COUNT(ip) FROM visits WHERE date(date) = date('2016-05-11') AND ip NOT IN (SELECT ip FROM visits WHERE date(date) = date('2016-05-11') GROUP BY ip ORDER BY count(ip) DESC LIMIT 2)`. Do you think this is correct? – Basj May 12 '16 at 09:05
  • @Basj Yes, this is correct: it returns the count for this specific date only, excluding the two first entries for this specific date. – Giorgos Betsos May 12 '16 at 09:07
  • I'm doing this to compute a correct "average number of visits per visitor", removing the bias of my own visits (100 visits per day ;) ). Now thinking about this, I should compute the *median number* of visits per visitor instead of average. Do you know how to compute such a *median* using SQL? – Basj May 12 '16 at 09:13
  • @Basj You can use variables for this. Check [this](http://stackoverflow.com/questions/1291152/simple-way-to-calculate-median-with-mysql) post out. – Giorgos Betsos May 12 '16 at 09:15
  • Seems quite complex, I'll keep your excellent solution for now :) – Basj May 12 '16 at 09:18