select daily average?

Anon

I'm entering multiple entries per day. I have a table named "NEWS".

Each news article has a "NEWS_ID"

what would be a query to find out the average number of new articles i've entered per day for this entire month?

jburfield

Are you also inputting a date into the table? If not, I don't think you'll be able to do this.

If you are, then you can select a count() of the entries for each day and divide them by the number of days in the month...

However, the date field is key.

-- Jason

Anon

thanks jason! i do have a date field, .. i was hoping i didn't have to do 30 queries to get 1 months results.

thanks for the time,
-mike

[deleted]

You don't.

How are you going to get daily averages with 30 queries anyway? you'd just get 30 results from 30 days, no averages at all.

Instead, just do a count() on all posts that have month=something, and devide the outcome by the number of days in the month.

Or grab all the totals per day and devide by the days:

SELECT count(*) AS messagecount
FROM posts
WHERE MONTH(date_field)= your_month_number

the loop through the results, adding the messagecount values up, and finally deviding by the number of messagecount values you added.

There are probably niftier ways using GROUP BY and AVG() though.

A forum, a FAQ, email notification, what else do you need?

Time to YAPF!: http://www.hvt-automation.nl/yapf/

jburfield

You obviously didn't read the original post (by me.)

What you state below is the same thing.

Count the entries per day, divide by number of days in the month.

Ex:
30 days totals to something like 600 entries.
Diving 600 by 30, you get a daily average of 20 entires per day.

Do you want to get really picky? If so, do you only want the average number of entries per days that you actually entered something? What if the month has 30 days, but you only entered something on 10 of those days. Do you want the average for those 10 days or for the entire month?

If so, you could so something similar to what you seemed to think I was saying before...but the original thought is quicker and easier.

-- Jason

vincent wrote:

You don't.

How are you going to get daily averages with 30 queries anyway? you'd just get 30 results from 30 days, no averages at all.

Instead, just do a count() on all posts that have month=something, and devide the outcome by the number of days in the month.