CODEWITHSUNDEEP
mysqlsql
Stwosch
Stwosch

Reputation: 642

function aggregate in function aggregate

I'm working with MySQL and I need to create long command that has to use many aggregate functions. That's my code ...

select count(field) from table group by field order by count(field) desc

and I need to SUM or AVG this, I don't know yet but it is irrelevant. When I try to do like this : 

select avg(count(field)) from table group by field order by count(field) desc

I get error. Is the way to use many of this functions in one query or maybe slice it?

The error is: #1111 - Invalid use of group function

Upvotes: 0

Views: 46

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

One method is a subquery:

select avg(cnt)
from (select count(field) as cnt
      from table
      group by field
     ) t;

A simpler method is to just do the calculation directly:

select count(*) / count(distinct field)
from table;

Upvotes: 1

Related Questions