CODEWITHSUNDEEP
sqlsqlite
ScottAlanTurner
ScottAlanTurner

Reputation: 1

How to get the Average on a count of grouped rows

In a single table I'm trying to group records on a column, count the total results in each group, and then get the average of the counts:

SELECT AVG(cnt) FROM 
   (SELECT COUNT(*) AS 'cnt'
   FROM birds
   GROUP BY birdID
);

So the table might look like:

birdID
------ 
Robin 
Robin 
Robin 
BlueJay 
BlueJay 
BlueJay 
Falcon

The counts for each:

Robin - 3
BlueJay - 3
Falcon - 1

And the overall average is 3 + 3 + 1 = 7 / 3 (total groups) = 2.3

My nested query works fine. But it seems like it could be done with one query, and not two.

AVG(COUNT(*)) doesn't work, though that's kinda what I'm trying to get at.

Sql returns the error 'misuse of aggregate function COUNT()' when using AVG(COUNT(*))

Upvotes: 0

Views: 68

Answers (1)

d r
d r

Reputation: 7776

This should do the job...

SELECT ROUND(AVG(CNT), 2) AS "AVERAGE_COUNT_OF_IDS"
FROM (
    SELECT BIRD_ID, COUNT(*) AS "CNT"
    FROM tbl
    GROUP BY BIRD_ID
);
/*    R e s u l t :
AVERAGE_COUNT_OF_IDS
--------------------
2.33                  */

... see it here https://sqlfiddle.com/sqlite/online-compiler?id=9be29b9c-3f57-49c8-afaa-5aeb598e3bc2

If you want it without subquery then try this:

--  transform counts to decimal values
Select Round( Round(COUNT(*), 2) / Round(COUNT(DISTINCT BIRD_ID), 2), 2) AS "AVG_CNT"
From tbl;
/*    R e s u l t :
AVG_CNT
---------
2.33      */

... see it here https://sqlfiddle.com/sqlite/online-compiler?id=9c5fb9ac-9631-4638-b0e6-6ccaaa37bc00

Upvotes: 2

Related Questions