CODEWITHSUNDEEP
mysqlsqlaveragemean
ChrisMe
ChrisMe

Reputation: 55

Average or calculate average

Is there a way to get average number of something if it isn't numbers?
My table:

ID|  CAT  |  TITLE  |     DATE            |
--|-------|---------|---------------------|
1 | CAT01 | TITLE01 | 2013-01-18 20:37:15 |
2 | CAT01 | TITLE02 | 2013-01-18 20:37:16 |
3 | CAT02 | TITLE03 | 2013-01-19 12:09:54 |

How could I get the AVG of posts per day?
For now I am using this:

SELECT AVG(cat) / DATEDIFF('2013-01-19', '2013-01-18') as average 
FROM posts 
WHERE date BETWEEN '2013-01-18' AND '2013-01-19'"

But for me it does not return average count. Maybe I should first count(cat) AS cnt and then use avg(cnt) AS average?
Or the AVG can not be used on non number tables?

Upvotes: 2

Views: 511

Answers (5)

fthiella
fthiella

Reputation: 49049

I think you just need to count the number of rows with COUNT(*), and add 1 to datediff:

SELECT COUNT(*) / (DATEDIFF('2013-01-19', '2013-01-18')+1) as average 
FROM posts 
WHERE DATE(date) BETWEEN '2013-01-18' AND '2013-01-19'

This will return the average number of posts, days that have no posts are counted.

Or, depending on what you are after, you might consider something like this:

SELECT
  COUNT(*) /
  (datediff(max(`date`), min(`date`))+1) as avg_posts
FROM
  posts

Upvotes: 0

jdurango
jdurango

Reputation: 543

Try this, formatted date yyyy-MM-dd to count all post per day and then take the average of each day

SELECT 
   amount/total AS AVG_POSTS_PER_DAY, day
FROM
  (
    SELECT COUNT(1) amount, DATE_FORMAT(DATE, '%Y-%m-%d') day,
    (SELECT COUNT(1) from posts) total
  FROM posts
  group by
    DATE_FORMAT(DATE, '%Y-%m-%d')) a
group by day`

Upvotes: 0

mechanical_meat
mechanical_meat

Reputation: 169284

You can do this in one step. A tested example may be found here: http://sqlfiddle.com/#!2/05760/12

SELECT 
  COUNT(*) / 
  COUNT(DISTINCT cast(`date` as date)) avg_posts_per_day
FROM 
  posts

Or you can do this in two steps:

  1. get posts per day,
  2. average the result of step 1.

A tested example may be found here: http://sqlfiddle.com/#!2/05760/4

SELECT 
  AVG(posts_per_day) AS AVG_POSTS_PER_DAY
FROM (    
  SELECT 
    CAST(`date` as date), 
    COUNT(*) posts_per_day
  FROM posts  
  GROUP BY 
    CAST(`date` as date)
) ppd

Upvotes: 3

Martin Bean
Martin Bean

Reputation: 39389

What you could do is the get the number of total posts, the number of days since the first timestamp in your database, and calculate an average from those two numbers.

Upvotes: 0

echo_Me
echo_Me

Reputation: 37233

try this

   SELECT COUNT(cat)/2  as avrag
   FROM posts

DEMO SQLFIDDLE

EDIT:

and if you want use the average between two dates just add this

     where date between 'the_date1' AND 'the_date2'

Upvotes: 0

Related Questions