CODEWITHSUNDEEP
mysqlaverage
Bob
Bob

Reputation: 177

Getting the average number of orders per day using mysql

I have the following table structure:

ID, User_ID, DateTime

Which stores a user id and datetime of an order purchased. How would I get the average number of orders a day, across every row?

In pseudo code I'm thinking:

  1. Get total number of orders
  2. Get number of days in range (from first row to last row).
  3. Divide 1. by 2. to get average?

So it would return me a value of 50, or 100?

Thanks

Upvotes: 1

Views: 4466

Answers (2)

Bohemian
Bohemian

Reputation: 425248

Since you know the date range, and you are not guaranteed to have and order on these dates, you can't just subtract the max(date) from min(date), but you know the number of days before you run the query, therefore simply:

select count(*) / <days>
from mytable
where DateTime between <start> and <end>

Where you supply the indicated values because you know them.

Upvotes: 2

Zohaib
Zohaib

Reputation: 7116

select DATEDIFF(NOW(), date_time) as days, AVG(count(*))
from table 
group by days

I have not tested the query, its just the idea, I guess it should work.

Upvotes: 0

Related Questions