php - Daily Average for 1 Week -
i attempting daily averages in query below, each day in 1 week period.
select avg(`carbs`) carbs, avg(`sugar`) sugar, avg(`units`) units, date_format(`trackedon`, '%m/%d/%y') date `tracking` `trackedon` between adddate(now(), interval -7 day) , now()
however, returning me 1 days worth of averages (but think it's averaging in 1 week, , displaying first day)
can me achieve this?
here dummy data, table structure:
-- -- table structure table `tracking` -- drop table if exists `tracking`; create table if not exists `tracking` ( `trackid` bigint(20) not null auto_increment, `userid` bigint(20) not null, `tracktype` int(11) not null, `carbs` decimal(10,3) not null, `sugar` decimal(10,3) not null, `units` decimal(10,3) not null, `trackedon` datetime not null, `tracklocation` geometry not null, primary key (`trackid`) ) engine=myisam default charset=latin1 auto_increment=11 ; -- -- dumping data table `tracking` -- insert `tracking` (`trackid`, `userid`, `tracktype`, `carbs`, `sugar`, `units`, `trackedon`, `tracklocation`) values (1, 3, 1, 36.000, 217.000, 9.343, '2013-08-06 17:38:37', ''), (2, 3, 1, 36.000, 316.000, 12.171, '2013-08-06 17:40:23', ''), (3, 3, 4, 12.000, 316.000, 7.671, '2013-08-06 17:42:24', ''), (4, 3, 5, 0.000, 316.000, 6.171, '2013-08-06 18:01:53', ''), (5, 3, 5, 0.000, 317.000, 6.200, '2013-08-07 08:25:06', ''), (6, 3, 2, 89.000, 317.000, 21.033, '2013-08-07 08:25:13', ''), (7, 3, 5, 0.000, 317.000, 6.200, '2013-08-07 08:27:19', ''), (8, 3, 3, 82.000, 168.000, 12.193, '2013-08-07 08:27:31', ''), (9, 1, 3, 86.000, 217.000, 13.000, '2013-08-07 08:28:08', ''), (10, 3, 5, 0.000, 317.000, 6.200, '2013-08-07 09:13:39', '');
my goal return daily averages of carbs
, sugar
, units
in between today (now()) , 7 days ago.
just add group date_format(trackedon, '%m/%d/%y')
end of query, , mysql summarize day:
select avg(`carbs`) carbs, avg(`sugar`) sugar, avg(`units`) units, date_format(`trackedon`, '%m/%d/%y') date `tracking` `trackedon` between adddate(now(), interval -7 day) , now() group date_format(trackedon, '%m/%d/%y')
Comments
Post a Comment