Simon, I think the Group by might work correctly, but sometimes (as in OP case) would require a lot of rewriting (copy-paste). The key point here is that the Window function doesn't change the set, but only allows wider access to other rows of the set at the current row "time". So we just have to move the group by to the column.
An example. The good answer to a PARTITION BY question is at https://stackoverflow.com/questions/2404565/sql-server-difference-between-partition-by-and-group-by#30907639 (I enforced the second popular answer) The table is CREATE TABLE [TableA] ([id] integer, [firstname] text, [lastname] text, [Mark] integer) and the PARTITION BY query from the answer select SUM(Mark) OVER (PARTITION BY id) AS marksum, firstname from TableA can be rewritten in SQlite as select (select sum(mark) from TableA grpTableA where grpTableA.id=TableA.id) as marksum, firstname from TableA The problem with the rewriting of OP query is that we have to move the whole FROM TblContractTasks ... part to the column with the group by sub-query. Not only the query becomes less readable, we will probably get some performance penalty unless Sqlite does some optimization for the column sub-query. But at least it's formally possible. Max On Fri, Apr 6, 2018 at 8:20 PM, Simon Slavin <slav...@bigfraud.org> wrote: > On 5 Apr 2018, at 11:41am, DThomas <d...@thomasres.net> wrote: > > > Select DISTINCT Sites.Customer, Sites.Digit, > > Count(TblContractTasks.TaskNumber) > > OVER (PARTITION BY Sites.Digit) As TaskCount > > FROM TblContractTasks INNER Join (Sites INNER Join TblContractDetails On > > Sites.Digit = TblContractDetails.SiteDigit) On > > TblContractTasks.ContractNumber = TblContractDetails.ContractNumber > > WHERE (Sites.Consultant='xx' ) ORDER BY Sites.Digit > > Instead of PARTITION BY use GROUP BY . See "with COUNT function" example > here: > > <http://www.sqlitetutorial.net/sqlite-group-by/> > > I think everything else used will continue to work. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
