Trying to get the average of a count resultset

Question

I have the following SQL:(bitemp)

SELECT COUNT (*) AS Count   FROM Table T  WHERE (T.Update_time =            (SELECT MAX (B.Update_time )               FROM Table B              WHERE (B.Id = T.Id)) GROUP BY T.Grouping 

now I am getting a resultset with a lot of numbers. I want to get the average of this list. At the moment, I am importing the list into excel and use its average function. But there is a AVG function for DB2, but I did not get it to work.

I tried SELECT AVG(COUNT(*)) and also SELECT AVG(*) FROM (theQuery).

Answer 1

You just can put your query as a subquery:

SELECT avg(count)   FROM      (     SELECT COUNT (*) AS Count       FROM Table T      WHERE T.Update_time =                (SELECT MAX (B.Update_time )                   FROM Table B                  WHERE (B.Id = T.Id))     GROUP BY T.Grouping     ) as counts 

Edit: I think this should be the same:

SELECT count(*) / count(distinct T.Grouping)   FROM Table T  WHERE T.Update_time =            (SELECT MAX (B.Update_time)               FROM Table B              WHERE (B.Id = T.Id))