CODEWITHSUNDEEP
mysqlsql
user962449
user962449

Reputation: 3873

Selecting the average of all values returned by mysql

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

Answers (2)

Justin
Justin

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

Randy
Randy

Reputation: 16673

select avg( c ) 
from ( SELECT  count(col1) as c FROM table1 GROUP BY col1 HAVING count > 1 )

Upvotes: 2

Related Questions