CODEWITHSUNDEEP
mysqlsqlh2
Catfoxes
Catfoxes

Reputation: 69

Average after group by in SQL query

select avg(select count(aid)
           from athlete
           group by codepays)

I get a "more than one row error". How with I go about getting the average of the result from my fist select ?

Upvotes: 0

Views: 212

Answers (2)

The Impaler
The Impaler

Reputation: 48770

You need to use a table expression (subquery).

For example:

select avg(cnt)
from (
  select count(aid) as cnt
  from athlete
  group by codepays
) x

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can do this using division:

select count(aid) / count(distinct codepay)
from athlete;

No subqueries are necessary.

(Although the arithmetic needs to be tweaked if codepay can actually be NULL.)

Upvotes: 0

Related Questions