COUNT(DISTINCT X) MySQL Query doesn't give results I need

In MySQL I have a query to count all of the browsers from a page_views table that I want to aggregate. Here's my query:

SELECT
    Browser,
    COUNT(*)
FROM page_views
WHERE PageID = 58
GROUP BY Browser;

which gives, for example:

Chrome            14
Firefox           17
Internet Explorer 9
Opera             5
Safari            3
--------------------
Total             48

This is exactly what I want. It works fine and it's fast. Now I wanted to to a distinct on the IP address to remove users that have viewed the page more than once. I added DISTINCT in the COUNT so it looks like this:

SELECT
    Browser,
    COUNT(DISTINCT IPAddress)
FROM page_views
WHERE PageID = 58
GROUP BY Browser;

This looks like it works but in reality, I don't think it does. In my real-world environment, I do aggregates on multiple columns, one for Browser, on for day of the week, one for the month etc... so it's one query each but with different GROUP BY. How I noticed that it may not be the result I want is that every one of my queries has a different number for totals. Aggregating the views by browsers gives 48 total views, by day of week gives 45 total views, by month gives 50 total views. Something's not right.

Also, if I do a simple SELECT COUNT(DISTINCT IPAddress) FROM page_views WHERE PageID = 58 I get much less page views that the other aggregate queries have as a total.

Again, what I want is the first query I put above but only aggregate once for each IPAddress so it basically calcu开发者_开发技巧lates the unique viewers of a page and not the total of views a page has.

What am I doing, or not doing correctly to get the results I want?

Thank you.

If you do:

SELECT
    Browser,
    day_of_the_week,
    month_of_the_year,
    COUNT(DISTINCT IPAddress)
FROM page_views
WHERE PageID = 58
GROUP BY Browser, day_of_the_week,month_of_the_year

The total of count(distinct) will no be the same as in your simple query.

If you want to eliminate duplicate IP-adresses in the same day, you'll need something like this:

SELECT 
  p1.Browser
  , p1.pageID
  , p1.WEEKDAY(`date`) as day_of_week
  , p1.MONTH(`date`) as The_month
  , COUNT(p2.Unique_visitors_this_day) as uniqueviews
FROM page_views p1
LEFT JOIN (SELECT id, 1 as Unique_visitors_this_day FROM page_views pv1
           LEFT JOIN page_views pv2 ON (pv1.id > pv2.id 
                                     AND pv1.ipaddress = pv2.ipaddress
                                     AND pv1.`date` = pv2.`date`)
           WHERE pv2.id IS NULL) as p2
       ON (p1.id = p2.id)
 WHERE p1.PageID = 58
 GROUP BY p1.Browser, p1.day_of_week, p1.The_month;