CODEWITHSUNDEEP
mysql
farrusete
farrusete

Reputation: 435

Mysql: query to get average, maximum and minimum from date range

I have a table with just three fields:

userID (integer, key, autoincrement)
date (Y-m-d format)
hits (integer)

in which I populate data every day (userID and date are unique) so i have values like:

1 | '2016-01-01' | 200
1 | '2016-01-02' | 289
2 | '2016-01-15' | 389
2 | '2016-01-16' | 390
....

How could I get:

t h a n k s

Upvotes: 0

Views: 262

Answers (2)

Leo Skhrnkv
Leo Skhrnkv

Reputation: 1703

SELECT AVG(hits) FROM table WHERE date BETWEEN 'beginning' and 'end';

here: 'beginning' is the start date of week or month in the format 'yyyy-mm-dd'; 'end' is the same, but for the end date.

SELECT MAX(hits) FROM table WHERE date BETWEEN 'beginning' and 'end';
SELECT MIN(hits) FROM table WHERE date BETWEEN 'beginning' and 'end';

Examples:

SELECT AVG(hits) FROM table WHERE date BETWEEN '2016-01-01' and '2016-01-31';
SELECT MAX(hits) FROM table WHERE date BETWEEN '2016-01-01' and '2016-01-31';

Upvotes: 1

Jose HLS
Jose HLS

Reputation: 136

For the Average daily hits for last week, try doing:

SELECT date, AVG(hits) 
FROM table 
WHERE date <= '2016-03-19' AND date >= '2016-03-13' 
GROUP BY date

Max and Min, substitute "AVG" for "MAX" or "MIN".

Upvotes: 1

Related Questions