Thanks a lot for respond my email! But the first row (20657220 1 2 101 -- this is 2009-04-11 00:00:00) may not be there in the dailyDataTable so min(startTime) won't work in this case. Any idea Igor? Thanks JP
________________________________ From: Igor Tandetnik <itandet...@mvps.org> To: sqlite-users@sqlite.org Sent: Wednesday, April 15, 2009 7:44:48 PM Subject: Re: [sqlite] Need help with the SQL statement. "Joanne Pham" <joannekp...@yahoo.com> wrote in message news:594788.4966...@web90305.mail.mud.yahoo.com > Hi All, > I have the following table(dailyDataTable) as defined below > startTime INTEGER : number of minutes in GMT time > appId INTEGER : application Id > remoteId INTEGER : server id > proxyCount INTEGER > This table can have up to 24 hours as below: (this table can have > only few rows but it can have up to 24 hours). > startTime appId remoteId proxyCount > 20657220 1 2 101 -- this is 2009-04-11 00:00:00 > 20657280 1 2 105 -- this is 2009-04-11 01:00:00| > ....................................................................... > 20658540 1 2 101 -- this is 2009-04-11 22:00:00 > 20658600 1 2 105 -- this is 2009-04-11 23:00:00 > > I need to take these data and insert into another > table(weeklyDataTable) with the following sql statement: > 1) login to weeklyDB > 2) Run the following sql statement > ATTACH DATABASE 'dailyDB' as DailyDB; insert into weeklyDataTable > select (strftime('%s',date(startTime * 60,'unixepoch')))/60 , appId, > remoteId, sum(proxyCount ) from DailyDB.dailyDataTable group by > appId, remoteId ; DETACH DATABASE DailyDB; " > > Result below in weeklyDataTable > 20656800 1 2 2386| -- this 2009-04-10 17:00:00 > But this is wrong I want to have this row below in the > weeklyDataTable as below. > 20657220 1 2 2386| -- this 2009-04-11 00:00:00 Perhaps something like this: insert into weeklyDataTable select min(startTime), appId, remoteId, sum(proxyCount ) from DailyDB.dailyDataTable group by appId, remoteId; Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
