CODEWITHSUNDEEP
mysqlaverage
dialogik
dialogik

Reputation: 9552

How to fetch average rows per day?

I have a table

id | created
---------------
 1 | 2014-07-01
 2 | 2014-07-01
 3 | 2014-07-01
 4 | 2014-07-01
 5 | 2014-07-02
 6 | 2014-07-03
 7 | 2014-07-04
 8 | 2014-07-05
 9 | 2014-07-05
10 | 2014-07-05

and I want to fetch the average of rows per day. So if it's

2014-07-01: 4 rows
2014-07-02: 1 row
2014-07-03: 1 row
2014-07-04: 1 row
2014-07-05: 3 rows

The average would be

sum 4 + 1 + 1 + 1 + 3 = 10 items
10 items / 5 days = 2 items/day

My SQL query is

SELECT AVG(COUNT(*))
FROM `mytable`
GROUP BY `created`

But I get this error

#1111 - Invalid use of group function

How do I have to modify my query in order to get a correct result?

Upvotes: 3

Views: 1012

Answers (3)

Charvee Shah
Charvee Shah

Reputation: 720

You can do this without subquery also.It is much fatser.

SELECT COUNT(*)/COUNT(DISTINCT `created`) FROM `mytable`;

Here is the example link: http://sqlfiddle.com/#!2/2c284df/2

Upvotes: 4

Kickstart
Kickstart

Reputation: 21513

Have a sub query to get the count per day, and then use that to get the average:-

SELECT AVG(day_count)
FROM 
(
    SELECT COUNT(*) AS day_count
    FROM `mytable`
    GROUP BY `created`
) sub0

Upvotes: 1

fancyPants
fancyPants

Reputation: 51888

You need to put it in a subquery.

SELECT AVG(my_count) FROM (
SELECT COUNT(*) as my_count
FROM
your_table
GROUP BY created
) sq

Upvotes: 4

Related Questions