SQL question

Pilly

Heres my query....

$sql = "SELECT field1,datestamp, count(*) AS cnt
FROM phplog
Group by cnt";

When i run it it says it cannot group by cnt, if i change it to ORDER BY i get "Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause"

what am i doing wrong?

ultraslacker

Violating the sql standard.

All columns in SELECT have to be either in an aggregate function (MIN, MAX,SUM,COUNT) or in a GROUP BY.

Pilly

The query works if I have group by field1 but not cnt

ultraslacker

You must be using mysql which will allow that sort of nonsense.

Sxooter

I'm not sure you get the idea of aggregation.

We use the group by to make a set of aggregated info, then the aggregate functions like sum(), avg() or count() can operate over that set. So, how can we group by a field that doesn't exist yet?

select max(field1), max(field2), count(*) as cnt
group by cnt

could be rewritten as:

select max(field1), max(field2), count() as cnt
group by count()

but how (or more importantly, WHY) do you want to group by count()??? Count() only has one output, the total row count, not very useful. I.e. you'd get ONE data set for aggregation.

Pilly

I use the query
$sql = "SELECT field1,datestamp, count(*) AS cnt
FROM phplog
Group by field1";

Which gives me results like
[field1] [cnt]
Dave 345
James 200
Ian 100
Steve 190
etc..

What i wanted to do was sort the list by cnt and get the top ten users.

Sxooter

then just add

order by cnt limit 10

or something like that to the end

Pilly

Resolved using.

$sql = "SELECT field1,datestamp, count(*) AS cnt
FROM phplog
Group by field1
ORDER BY cnt DESC
Limit 10
";