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).

Solution

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))

Comments

[Xavjer]

The question is pretty old and solved, no need of any sample from my point of view

[Xavjer]

i think this is getting me the average of each result, which is one (because its grouped) but i want to get the average of the count content, so if i recieve 2,4,6 the avg would be 4 , but i receive 1 (because there's only 1 of each)

[DavidEG]

If the subquery "counts" returns 2,4,6 then the avg over this subquery will return 4. Could you post in your question the result SELECT * FROM Table T WHERE T.Update_time = (...)? This may be helpful to help you.

[Xavjer]

Well, i found a solution to my problem, thanks to your subquery example. I am using it as follows: SELECT CAST(sum(Count) AS FLOAT) / CAST(COUNT(Count) AS FLOAT) ... this works as desired, thanks ;) (however I don't know why avg returns 1, even when cast as float. the result is 1.91...

[Xavjer]

Ok, found the perfect solution, using SELECT AVG(CAST(COUNT AS FLOAT))