Thank you Igor. The GROUP BY was the secret (I was trying to do a GROUP BY on the outer select, but it wasn't quite cutting it).
GROUP BY is very powerful, but I notice it has a performance cost. Is there a way to use an index with it? I have EventTime indexed and that index is being used. I suppose creating a combined index of EventTime, ProcessID and FileName might help because the underlying record wouldn't need to be looked up. Any thoughts on that idea? Thanks Doug > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On > Behalf Of Igor Tandetnik > Sent: Tuesday, May 27, 2008 12:24 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Query help? > > Doug <[EMAIL PROTECTED]> wrote: > > SELECT ProcessID, count(1), sum(BytesProcessed) > > FROM FinishedWork > > WHERE EventTime > {20 minutes ago} > > GROUP BY ProcessID, FileName > > > > Unfortunately when a file is processed twice, it's counted twice (ie > > added into the sum twice) and I need to show only unique work, so I > > need to count each processID-FileName pair only once for the given > > timeframe. > > Try this: > > SELECT ProcessID, 1, BytesProcessed > FROM FinishedWork > WHERE RowId IN ( > select RowId from FinishedWork > WHERE EventTime > {20 minutes ago} > GROUP BY ProcessID, FileName > ); > > 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
