5

I have a mysql table like:

id, visitorid, pageid

When a visitor hits the website it stores their visitor id and the page id as a row.

I am trying to extract the number of visitors who hit the website exactly X times. (for a chart). so how many visit just one page, how many visit 2 pages...

so far I have:

SELECT COUNT(visid),visid FROM vislog GROUP BY visid ORDER BY COUNT(visid) DESC

But I don't know how to do the next part of counting the counts.

Is it possible to do as a MySQL query?

Dharman
  • 30,962
  • 25
  • 85
  • 135
David19801
  • 11,214
  • 25
  • 84
  • 127
  • You mean GROUP BY based on a GROUPY BY result? – hakre Oct 07 '12 at 10:30
  • I think I mean count(count(visid)) and count(visid). so number who visited once was xx and visited twice was yy and... – David19801 Oct 07 '12 at 10:35
  • You can (and probably should) add your answer as an answer, not just an edit of the question. ) – raina77ow Oct 07 '12 at 10:42
  • You actually *should* add your edit as an answer (but it's fair to keep the other answer accepted if it helped you because it's a way to say thank you). – hakre Oct 07 '12 at 10:44
  • okay, I have added it now but it says wait 2 days to accept so I chose the other answer for now. – David19801 Oct 07 '12 at 10:45
  • 1
    @hakre: Does the OP's answer add any value? It's basically the same as mine, with a few aliases renamed. – ypercubeᵀᴹ Oct 10 '12 at 11:42
  • 1
    @ypercube: Yes this seems fishy. – hakre Oct 10 '12 at 11:44
  • @hakre, what do you mean fishy? My answer was correct and worked. The code was simple and easy for me to understand. ypercube had complicated code. For someone searching the answer I think mine is better...I will not revenge downvote his like he has done to mine. I just want things to be clear to searchers because I use this site too. – David19801 Oct 10 '12 at 13:33
  • 4
    @David19801: In my eyes, you should pay more respect for those that helped, yes. You were able to compile your answer *after* others have shown you the way. It's fishy then to accept your own answer later on, you can leave your answer here and if future users find it useful, they will upvote it. And it is not true that ypercube's code is harder to understand for the general audience. It is for you because it's your code, others need to first read the question, see if it fits their need and then a well documented answer is better because it helps. – hakre Oct 10 '12 at 14:48
  • I hope you can understand that. Suggestion: Reconsider where you want to place your acceptance and I'll reconsider my voting, too. – hakre Oct 10 '12 at 14:49
  • @David19801: So I do the first step, I removed my downvote. – hakre Oct 10 '12 at 20:29
  • 2
    For the record, I did not revengefully downvoted. And hakre has not posted answer here, so (if he downvoted), where's the revenge? People downvote for various reasons. Maybe your answer does not have very clear code (or others think so) or it is a copy of mine (or others think so). That's why downvotes are anonymous, so there are no revenges. – ypercubeᵀᴹ Oct 11 '12 at 10:12

5 Answers5

14

You can wrap your query inside another one:

SELECT
    cnt      AS page_visits
  , COUNT(*) AS number_of_visitors
FROM
    ( SELECT 
          COUNT(*) AS cnt                --- use: COUNT(DISTINCT page_id)
                                         --- for a different count
      FROM vislog 
      GROUP BY visid
   ) AS grp
GROUP BY cnt 
ORDER BY number_of_visitors ;

or (I suppose this makes more sense for passing the numbers to a chart), remove the ORDER BY which is the same as putting:

ORDER BY cnt ;
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
11

One way to do it is to wrap this query into another one:

SELECT COUNT(visid) FROM (
    SELECT COUNT(visid) AS cvisid, visid 
      FROM vislog 
  GROUP BY visid 
  HAVING cvisid = 2) AS c

But I think you need to get the histogram of visits: this can be done with PHP (assuming the query is the same as in the question):

$results = array();
// query preparation skipped, as it's obviously done by the OP himself
while ($row = $sth->fetch()) {
  $count = $row['cvisid'];
  if (isset($results[$count])) {
    $results[$count]++;
  }
  else {
    $results[$count] = 1;
  }
}

Or with MySQL itself:

SELECT cvisid, 
       COUNT(cvisid) AS cnt 
  FROM (
    SELECT visid,
           COUNT(visid) AS cvisid 
      FROM vislog 
  GROUP BY visid ) AS c
GROUP BY cvisid
Quicksilver
  • 2,546
  • 3
  • 23
  • 37
raina77ow
  • 103,633
  • 15
  • 192
  • 229
  • What is HAVING? I have never seen that before – David19801 Oct 07 '12 at 10:23
  • @raina77ow: for reporting it's normally better to have a SQL query, not a PHP script. Just saying. Naturally you could calculate everything in PHP, however the database is normally better with that. – hakre Oct 07 '12 at 10:31
  • @hakre Well, there's a way to do a histogram in MySQL, but I admit I never done it over 'inner histograms'. ) Will update my answer, though. – raina77ow Oct 07 '12 at 10:34
  • @raina77ow : I doubt this query may result in an error `1248 - Every derived table must have its own alias` – Quicksilver Oct 10 '12 at 11:58
  • @KhaleelAKarim Thank you, fixed this and another (syntax) typo. – raina77ow Oct 10 '12 at 16:07
1

Raina77ow (2nd reply) returned a clean solution in his third block of code

    SELECT cvisid, 
           COUNT(cvisid) AS cnt 
      FROM (
        SELECT visid,
               COUNT(visid) AS cvisid 
          FROM vislog 
      GROUP BY visid ) AS c
    GROUP BY cvisid

thank you

Robert
  • 490
  • 1
  • 5
  • 17
0

Try using following query

SELECT COUNT(a.page_visits) AS no_of_visitors, a.page_visits AS page_count
FROM(
SELECT COUNT(DISTINCT pageid) AS page_visits
FROM vislog
GROUP BY visid) AS a
GROUP BY a.page_visits;

Hope it helps...

jsist
  • 5,223
  • 3
  • 28
  • 43
  • Yes, this but how to do for all numbers so I don't have to run the query for every number menually? can it all be done with just a query? – David19801 Oct 07 '12 at 10:27
-11

I could solve it this way:

SELECT cnt, COUNT(cnt) FROM (
    SELECT COUNT(visid) as cnt FROM vislog GROUP BY visid
) x GROUP BY cnt ORDER BY cnt ASC

The little x is important.

casperOne
  • 73,706
  • 19
  • 184
  • 253
David19801
  • 11,214
  • 25
  • 84
  • 127
  • How is that little `x` called and how does it work? And what do you do with the new table then? Don't you need to remove it from the database after you've executed this query? ;) – hakre Oct 07 '12 at 10:47
  • I have no idea. I just know it works and was giving table error when I did not have it. Do I need to remove it? – David19801 Oct 07 '12 at 10:48
  • I don't think so, but I'd say it is generealy useful, after making such a discovery, to actually understand it. One way to see if oneself understands something is explaining it to others, e.g. in the answer. This is how a Q&A site works best. – hakre Oct 07 '12 at 10:51
  • You can search the Mysql manual. I think it is with subqueries. Also you don't need to make that bold. Everybody sees that it is by you. I also placed a link in your question, so it is really prominent. – hakre Oct 07 '12 at 10:59
  • 6
    The `x` is called an alias. Derived tables (as in this case) have to have an alias. You can use either `AS x` or just `x` (the `AS` is optional). It's the same as the `AS grp` in my answer and the `AS a` in another answer. – ypercubeᵀᴹ Oct 07 '12 at 11:01
  • I see, so it is not a real table, just a pretend table for mysql so mysql can treat it like a table? – David19801 Oct 07 '12 at 11:02
  • 1
    It's not a **base** table, not even a temporary table, it's a derived table. It's still a table though (with a very small lifetime). The `x` is the name of this derived table (which is probably evaluated for every execution of the query and then removed). You don't have to do anything to remove it. You can't keep it actually, it's all done internally by the sql engine. – ypercubeᵀᴹ Oct 07 '12 at 11:05
  • 2
    I fail to see how this is different from the answer @ypercube provided. – swasheck Oct 18 '12 at 16:22