Reputation: 3873
How would I find the average from this query using SQL:
SELECT count(col1) as count FROM table1 GROUP BY col1 HAVING count > 1
I'm trying to find the average number of rows per col1
So far I managed to find the total amount of rows per col1
, now I just need the avg
Upvotes: 0
Views: 961
Reputation: 9724
For table you need alias and change count
to count(*)
.
Query:
SELECT avg(t1.c) AS avgcol1
FROM
(SELECT count(col1) AS c
FROM table1
GROUP BY col1 HAVING COUNT(*) > 1) t1
Upvotes: 0
Reputation: 16673
select avg( c )
from ( SELECT count(col1) as c FROM table1 GROUP BY col1 HAVING count > 1 )
Upvotes: 2