On 2018/04/06 9:15 PM, Don V Nielsen wrote:
That seems like an odd application of OVER (Partition by). Is there some
performance reason one would want to do DISTINCT OVER (PARTITION BY)
instead of a simple GROUP BY Sites.Customer, Sites.Digit, Count()?
Agreed, in fact half that query seems redundant, it would work, but my
guess is Mr. Thomas inherited it from someone else who did not fully
know what they wanted to achieve, then googled a solution and found a
hit on an old stackoverflow question that was sort-of like what they
wanted, but not exactly.
The reason I say that is that the OVER (PARTITION BY) functionality in
MSSQL /can/ produce some amazing results that are very hard to do in
SQLite (not impossible though, just difficult), but the shown query is
not such a case, it's a straight-forward grouping, unless the query was
simplified for posting, or the expected results were different than what
we assume.
As to the solution, It's a bit like asking how you say "Cowabunga dude!"
in Swahili - there is no amount of translation that would reproduce the
sentiment. I think the tutorial from Simon's post will shed light, but
Dean, if you still have trouble, could you perhaps show us your schema
and explain what you want to know, because now we simply assume that
shown query actually gives exactly what you wanted, which may or may not
be the case. Tell us what the query output should reveal or look like,
and someone here (or several someones) should come up with real good
queries for the job.
On Fri, Apr 6, 2018 at 12: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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
