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).
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))
Source: Stackoverflow.com