CODEWITHSUNDEEP
phpmysqlicountdistinct
Snappysites
Snappysites

Reputation: 843

Calculate most common value, amount of times it occurs and without using reoccurring values

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

Answers (1)

BeetleJuice
BeetleJuice

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

Live demo

Upvotes: 2

Related Questions