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

The question is pretty old and solved, no need of any sample from my point of view — Xavjer, Jul 25 '17 at 11:20

DavidEG · Accepted Answer · 2011-10-12T09:37:00.490

103

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

edited Oct 12 '11 at 09:37

answered Oct 12 '11 at 09:26

DavidEG

5,857
3
29
44

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) – Xavjer Oct 12 '11 at 09:50
1

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. – DavidEG Oct 12 '11 at 10:09
4

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 Oct 12 '11 at 10:19
8

Ok, found the perfect solution, using `SELECT AVG(CAST(COUNT AS FLOAT))` – Xavjer Oct 12 '11 at 11:24

Trying to get the average of a count resultset

1 Answers1