COUNT(*) … GROUP BY on two fields

Suppose I had the following table:

+-------+--------+-------+
| HitID | UserID | page  |
+-------+--------+-------+
| 1     | 581931 | index |
| 2     | 613192 | index |
| 3     | 581931 | index |
| 4     | 581931 | index |
| 5     | 613192 | index |
| 6     | 794164 | index |
| 7     | 794164 | music |
| 8     | 991027 | index |
| 9     | 581931 | index |
| 10    | 613192 | music |
| 11    | 581931 | music |
| 12    | 581931 | music |
| 13    | 613192 | index |
| 14    | 794164 | music |
| 15    | 794164 | index |
+-------+--------+-------+

If I run the following query on this table:

select count(*) as cnt, page
from Hits
group by page, UserID

I get:

+-----+-------+
| cnt | page  |
+-----+-------+
| 3   | index |
| 3   | index |
| 2   | index |
| 2   | music |
| 1   | index |
| 1   | music |
| 2   | music |
+-----+-------+

In other words, it counts the number of rows per unique page-UserID combination. What I want to do is get the number of rows per unique page, but limit that to only the number of unique UserIDs that visited that page. Using the example above, the result table I want would be:

+-----+-------+
| cnt | page  |
+-----+-------+
| 4   | index |
| 3   | music |
+-----+-------+

because 4 users visited the index page at some point, and 3 users visited the music page at some point.

How can this be achieved?

SELECT page
     , COUNT(DISTINCT UserID) AS cnt
  FROM Hits
GROUP 
    BY page

:cool:

Awesome! Thanks for the quick response! :smiley: