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

Popular posts from this blog

plot - Remove Objects from Legend When You Have Also Used Fit, Matlab -

java - Why does my date parsing return a weird date? -

Need help in packaging app using TideSDK on Windows -