select count(ipaddr)

cdpapoulias

Hello...

How do I get

select count(ipaddr) from weblog where vdate = "2001-02-03";

to add up the result for an entire month?

Thanks,
Dinos

brandonschnell

SELECT count(ipaddr) FROM weblog WHERE vdate LIKE "2001-02-%";

use can use concat() to create the str and you can get the date and format it using date_format()

cdpapoulias

Brandon - Thanks for helping...

Still have another question...

select ipaddr, count(*) from weblog where vdate = current_date group by ipaddr

this of course sums up (if there are multiple entries) for each ipaddr and reports them. my problem is that I get three rows returned...and what I really want is the number "3". how can I run the above query but get the number 3 returned instead?

I wrote this in PHP:

$sql_result = mysql_query ("select ipaddr, count(*) from weblog where vdate = current_date group by ipaddr");
$ccount = 0;
while ($row = mysql_fetch_array($sql_result)) {
	$ccount = $ccount + 1;
}	
echo $ccount . " visitors<br>";

but I would love to see a more elegant solution in mysql.

Thanks again!
Dinos

brandonschnell

distinct visitors:

SELECT count(DISTINCT ipaddr) FROM weblog WHERE vdate = current_date;

http://www.mysql.com/doc/G/r/Group_by_functions.html

Anon

$total = mysql_result(mysql_query("select count(ipaddr) from weblog where vdate = \"2001-02-%\""),0);

That'll do it.

Anon

Whoops. Replace vdate = with vdate LIKE

tommyboy

Was posting in a hurry, sorry about that.

The following should do the job:

$total = mysql_result(mysql_query("SELECT DISTINCT COUNT(ipaddr) FROM weblog WHERE vdate LIKE \"2001-02-%\""),0);

sepodati

If you want a count of all IP addresses for the current month, use:

SELECT COUNT(ipaddr) FROM your_table WHERE MONTH(NOW()) = MONTH(vdate);

If you want a count for each individual IP address within the current month, use this:

SELECT ipaddr,COUNT(ipaddr) FROM your_table WHERE MONTH(NOW()) = MONTH(vdate) GROUP BY ipaddr;

Hope that helps...

---John Holmes...

sepodati

You get the same result with or without the DISTINCT...

mysql> select * from a;
+------+
| id |
+------+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 1 |
| 2 |
| 2 |
| 44 |
| 5 |
| 5 |
| 78 |
+------+
13 rows in set (0.00 sec)

mysql> select distinct count(id) from a where id like '4%';
+-----------+
| count(id) |
+-----------+
| 2 |
+-----------+
1 row in set (0.28 sec)

mysql> select count() from a where id like '4%';
+----------+
| count() |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)

---John Holmes...

cdpapoulias

Okay...

This is what I have:

mysql> select ipaddr,count() from weblog group by ipaddr;
+---------------+----------+
| ipaddr | count() |
+---------------+----------+
| 138.88.68.253 | 19 |
| 138.88.69.66 | 6 |
| 138.88.71.29 | 1 |
| 24.239.22.147 | 11 |
+---------------+----------+
4 rows in set (0.01 sec)

in PHP, how can I get to the "4" in the "4 rows in set"?

19 accesses from 68.253, 6 from 69.66, etc. which isn't what I want.

Thanks again...all...
Dinos

brandonschnell

i'll repeat my previous post as it seems to have been ignored:

distinct visitors:

SELECT count(DISTINCT ipaddr) FROM weblog WHERE vdate = current_date;

http://www.mysql.com/doc/G/r/Group_by_functions.html

mysql> select * from test;
+-------------+
| ipaddr |
+-------------+
| 192.168.0.1 |
| 192.168.0.1 |
| 192.168.0.2 |
| 192.168.0.3 |
| 192.168.0.3 |
| 192.168.0.3 |
| 192.168.0.4 |
| 192.168.0.5 |
| 192.168.0.6 |
+-------------+
9 rows in set (0.00 sec)

mysql> select count(distinct ipaddr) from test;
+------------------------+
| count(distinct ipaddr) |
+------------------------+
| 6 |
+------------------------+
1 row in set (0.00 sec)

to return the result:
$result = mysql_db_query("db","SELECT count(distinct ipaddr) FROM test");
$row = mysql_fetch_array($result);
echo $row[0];

if you do insist on using the "ipaddr,count(*)" method then use mysql_num_rows() to count the returned rows.

typically "count(distinct ipaddr)" is going to be a lot faster on a large table then will be mysql_num_rows(). i've posted previously about the speed issue so search on phpbuilder for some benchmarks.

cdpapoulias

Brandon:

This is what I get:

mysql> select count (distinct ipaddr) from weblog;
ERROR 2000: You have an error in your SQL syntax near '(distinct ipaddr) from we
blog' at line 1
mysql>

I'm running mysql 9.33 distrib 3.22.25 on solaris.

Is my distro that my hosting provider uses odd?

-Dinos

brandonschnell

not odd, but an old version of the software.

it'll work on mysql 3.23. i've tried it with the 3.23 beta so i know it works. look at the mysql manual (http://www.mysql.com/doc/G/r/Group_by_functions.html) for an example of using "count(distinct column_name)"

see if you can get your hosting provider to upgrade to 3.23.

otherwise use mysql_num_rows() with the query you have now to get the total. its ugly, but it works.

tommyboy

The problem is that you are using DISTINCT within the count arguement. You use it after select, so SELECT DISTINCT COUNT(...)

brandonschnell

so lets break that down. count() returns a count of every row and is limited by the where clause.

distinct count() returns only unique count() values. since count() only returns one value the distinct will have no effect.

"select distinct count()" is exactly the same as "select count()" both of which result in counting every single row which isn't what the this guy wants (Dinos, say something if this isn't true).

from the example sql he has posted (but he hasn't specifically said so) it looks like he doesn't care about the total ip addresses but the total UNIQUE ip addresses. so if one person visits the site once but another person visits the site 10 times he wants a query that will return 2 hits (two unique ip addresses and presumably completely different people. the count will go down for users connecting via a proxy server and up for modem users. and lets not forget all of those siamese twins surfing the net, would you count that as one hit or two?!?)

to get the total UNIQUE ip addresses "SELECT count(DISTINCT ipaddr)" should pull out all of the non null ipaddr rows, return the distinct rows to count(), and count() will return the sum() of UNIQUE ip addresses. if this isn't what Dinos Papoulias wants then he'll let us know.

its in the manual:

http://www.mysql.com/doc/G/r/Group_by_functions.html

cdpapoulias

Brandon, Tom & All,

Thanks for your help!

select count (distinct ipaddr) is exactly what I need...

It was pointed out to me, upon trying that statement, that it will not work with 3.22 which my hosting provider (communitech.net) uses but will with 3.23.

So...I'm contacting them to find out how that can be done.

You guys are awesome!
Dinos

Brandon Schnell wrote:

so lets break that down. count() returns a count of every row and is limited by the where clause.

distinct count() returns only unique count() values. since count() only returns one value the distinct will have no effect.

"select distinct count()" is exactly the same as "select count()" both of which result in counting every single row which isn't what the this guy wants (Dinos, say something if this isn't true).

to get the total....