Mysql Error: Invalid use of group function

[stmadeveloper]

Running into a problem with a mysql select statement - my head says this should work - but apparently something with group by has changed -- or I'm just wrong.

$sql = "select COUNT(statsid), pagehere from " . $tableprefix . "stats group by pagehere order by COUNT(statsid) desc";

Any suggestion on a fix..........

 

[kblessinggr]

kind of hard to count the id and have another field in there at the same time. I think you need to use the DISTINCT option, since it seems like maybe you're counting the entire thing and not on a per pagehere basis.

 

[Icecube]

there's a count at the end of the statement that shouldn't be there

 

[kblessinggr]

there's a count at the end of the statement that shouldn't be there

He might have to do COUNT(fieldname) as new_field name then change the count(fieldname) at the end to just new_field.

 

[Refrozen]

Yes, you can't call COUNT() in the ORDER BY clause if you've already called it in the SELECT. Alias the result and ORDER BY that.

 

[Insomniac]

$sql = "select pagehere, count(*) as cnt from {$tableprefix}stats group by 1 order by 2 desc";

 

[stmadeveloper]

$sql = "select pagehere, count(*) as cnt from {$tableprefix}stats group by 1 order by 2 desc";

So this seems to work (limited test).. but still wrapping my head around it

So to return values --- for example:

This returns a value - $result['pagehere'];

This isn't returning a value.........

$result['cnt(statsid)'];

How would I grab that......

 

[Insomniac]

$result['cnt'];

 

[stmadeveloper]

Great - that makes sense. Gracias.