Mysql: Calculate visit frequency

Question

I have this table

CREATE OR REPLACE TABLE hits (ip bigint, page VARCHAR(256), agent VARCHAR(1000), 
                              date datetime)

and I want to calculate googlebot visit frequency for every page.

... WHERE agent like '%Googlebot%' group by page

score 4 · Accepted Answer · answered Sep 18 '10 at 20:54

4

Use:

  SELECT page,
         COUNT(*)
    FROM hits
   WHERE agent LIKE '%Googlebot%'
GROUP BY page

answered Sep 18 '10 at 20:54

OMG Ponies

score 1 · Answer 2 · answered Sep 18 '10 at 20:55

1

Try this:

select page, count(1) as visits
  from hits
 where agent like '%Googlebot%'
 group by page;

answered Sep 18 '10 at 20:55

Pablo Santa Cruz

@stereofrog: It's basically the same. You just care about counting rows, so count(1) is all you need. Check this out: http://stackoverflow.com/questions/2710621/count-vs-count1-vs-countpk-which-is-better – Pablo Santa Cruz Sep 18 '10 at 21:34

2 Answers2