Query construction help

Kudose

Hello all. I am having a bit of trouble figuring out how to find the number of unique IPs per day, then adding them all together via MySQL.

I have the DB laid out like this:

hid | date | ip | userid | page
1 2006-04-13 12:45:12 64.XXX.2XX.XX X index.php

hid is a primary key that auto_increments. Date is a MySQL timestamp created with NOW() and IP is ... the IP.

I am trying to do something like this:

Get DISTINCT days (somehow ...), get DISTINCT IPs from those days, then SUM the IPs.

Catch my drift?

Any help would rock.

TIA.

devinemke

i cannot see why you would would to SUM() the ip's (add them together). i think what you mean is to COUNT() them.

SELECT COUNT(DISTINCT ip), DATE(date) AS date_only FROM table GROUP BY date_only

laserlight

hmm... the best I can see is with a subquery:

SELECT SUM(x) FROM (SELECT COUNT(DISTINCT(ip)) AS x FROM tablename GROUP BY date);

Though if subqueries dont work on your version of mysql, I suppose you can always sum in PHP.

Roger_Ramjet

EH! Come on lads, you both know better than that. What's with the distinct in a group by query??? Group by already returns each distinct value in the grouped column.

So, Kudose, you want to know how many distinct IPs there are, how many each day, and how many hits each has. By day or in total? No matter, boith can be done with WITH ROLLUP .

Basic hit count per day per ip

$sql = "SELECT DATE(date) AS do, ip, COUNT(hid) FROM table GROUP BY do, ip";

// PS this is mysql specific code and will not work on many other dbs
[code=php]

That will return the unique ips for each date and the hit count each has. If you just want the unique ips per date then 

[code=php]
$sql = "SELECT DATE(date) AS do, COUNT(ip) FROM table GROUP BY do";
[code=php]

Now to extend it to give the running total for all dates you just add the rollup

[code=php]
[code=php]
$sql = "SELECT DATE(date) AS do, COUNT(ip) FROM table GROUP BY do WITH ROLLUP";
[code=php]

The last record will be a NULL date and the total ips from all dates. Of course any ip that features in multiple dates will be counted multiple times.

Finally, if you just want the total unique ips regardless of date then

[code=php]
[code=php]
$sql = "SELECT COUNT(ip) FROM table GROUP BY ip";
[code=php]

Kudose

...lol... My SUM is supposed to be COUNT.

I will tool around with all of your suggestions and post back. Thanks 🙂

edit My host is lame and is running MySQL 4.0.15 so subselects is out /edit