Reputation: 843
This is basically a statistics script for website visitors. I have a very simple table like this:
website_ref | ip_address | city | page_viewed
abc 12.13.14 London index.php
abc 12.13.14 London contact.php
abc 12.13.14 London about.php
abc 10.16.19 Paris index.php
abc 33.33.33 Paris about.php
abc 44.42.32 London index.php
abc 09.09.09 Paris images.php
There's more information at play here but not relevant for this question. What I am trying to do is calculate the most common city for the website ref by visitors. Now looking at the table above you may think London
, as does my current script. But it is incorrect for my requirements as the IP addresses are the same for London, in fact, the top city should be Paris at it is the most common value with different IP Addresses. I hope you understand what I am trying to accomplish. I would also prefer that the statement could count the results too, so it would show as:
Most common: Paris (3/5)
5 being the total distinct IP Address visitors (which I can already figure out). Here is what I am using at the moment:
$getCITY = mysqli_fetch_assoc(mysqli_query($conn, "SELECT city, COUNT(city) as count FROM all_website_stats WHERE website_ref = '$website_ref' GROUP BY city ORDER BY COUNT(city) DESC LIMIT 1"));
$getCITY = $getCITY['city'];
$getCOUNT = mysqli_fetch_assoc(mysqli_query($conn, "SELECT COUNT(distinct ip_address) as count FROM all_website_stats WHERE website_ref = '$website_ref' AND `city` = '$getCITY'"));
$getCOUNT = $getCOUNT['count'];
I hope somebody can help me out here.
Upvotes: 0
Views: 29
Reputation: 40946
I would run this query:
select city, count(distinct ip_address) as ips,
(
SELECT count(distinct ip_address)
from all_website_stats where website_ref = 'abc'
) as total
from all_website_stats
where website_ref = 'abc'
group by city
order by ips desc
limit 1
To get the following result:
city | ips | total
Paris | 3 | 5
Upvotes: 2