Playing w/ COUNT, GROUP BY, & ORDER BY

[yarub]

So I'm kind of lost as to how I'd do this. Hopefully someone can help. I track every ip that views a certain page on my site. If it's their first view, it goes into the row as firstvisit='1'. If they come back at a later time (at least 180 seconds later), it tracks it as 0.

What I'm trying to do is create an output that would show the IPs and how many times they've viewed the page. Basically, I want to count all rows in the table; grouped by IP. I can do that part. That's not the problem. My problem is that I want to be able to print them out so that it descends from the IPs who have visited most to the people who only came the one time.

1.2.3.4 || 10 Views
4.5.6.7 || 8 Views
6.7.8.9 || 4 Views
2.7.3.3 || 1 View
4.8.2.6 || 1 View
3.5.7.9 || 1 View

[code]$query = mysql_query("select COUNT(id), id, ip, dateline FROM tablename GROUP BY ip");
while($row = mysql_fetch_array($query)){[/code]

Obviously this only does the first part of what I need. I don't know how to print them out so that it descends from the most views to the least. Can anyone help?

[btherl]

Add [code=php:0]ORDER BY count(id) DESC[/code] at the very end, assuming that is what you want to order by.

Alternitavely you can fetch the data and sort it in php, but this is the mysql forum :)

[yarub]

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in ...

[btherl]

Change your query to this:

[code=php:0]$sql = "select COUNT(id), id, ip, dateline FROM tablename GROUP BY ip";
$result = mysql_query($sql) or die("Error in $sql: " . mysql_error());[/code]


That will display what the error was.. then we can fix it based on that :)

[yarub]

Error in select COUNT(id), id, ip, dateline FROM tablename GROUP BY ip ORDER BY COUNT(id) DESC: Invalid use of group function

The weird part is that it doesn't give the error if the ORDER BY isn't on there. O_o

[joshi_v]

Hmm!
Group by part is wrong in your query.

You can't use ORDER BY COUNT(ID) in query.

It shoudl be like

GROUP BY ip ORDER BY id DESC


Regards
Joshi.

[yarub]

That wouldn't print the information out the way I want it though. That would just display the highest id in the table and descend from there. I want it to display the highest total views and down from there.

[joshi_v]

Then you can go for temporary table.
Because the count(id) will work only for that line..can't caompare it with other row values.
So my suggestion would be , first store the result in temporary table and then execute the query order by that count column.

Regards,
Joshi.

[btherl]

Hmph.  Well, you can order by aggregates in postgresql.  If mysql doesn't let you do that, then make it a subquery:

[code=php:0]SELECT * FROM (select COUNT(id) AS count_id, id, ip, dateline FROM tablename GROUP BY ip) AS foo ORDER BY count_id DESC[/code]


Semantically that's identical, but it may be less efficient due to use of a subquery.

[fenway]

You could try re-writing that as a self-JOIN... and see which explain gives better results.

[shoz]

Note that before MYSQL 4.1 you have to order by an alias put on the aggregate function's column.
[code]
SELECT COUNT(..) AS cnt FROM .. ORDER BY cnt
[/code]