On Thu, Jul 6, 2017 at 4:17 PM, Dominique Devienne <ddevie...@gmail.com>
wrote:
> On Thu, Jul 6, 2017 at 4:07 PM, Keith Medcalf <kmedc...@dessus.com> wrote:
>
>>
>> select sum(code_type == 'SET') as "#sets",
>> sum(code_type == 'CST') as "#constants",
>> sum(code_type == 'CMT') as #comments",
>> sum(code_type == 'LOG') as "#logs"
>> from keys;
>>
>> will do a single table scan and return the sums in a single statement
>> with no complications ...
>
>
> But my 1 line summary puts stats from several tables in columns.
>
> When my substitute the 4 scalar select expressions with yours, I get this
> error:
> Error 1 "only a single result allowed for a SELECT that is part of an
> expression."
>
with
kcounts(cset, ccst, ccmt, clog) as (
select sum(code_type == 'SET'),
sum(code_type == 'CST'),
sum(code_type == 'CMT'),
sum(code_type == 'LOG')
from well_keys
),
...
select ...,
(select cset from kcounts) as "#sets",
(select ccst from kcounts) as "#constants",
(select ccmt from kcounts) as "#comments",
(select clog from kcounts) as "#logs",
...;
But if I move it as a CTE view, this also achieves the 3,060ms time
equivalent to Clemens' solution, FWIW.
(w/o the index. If I recreate the index, it stays at 3s, apparently that
formulation makes the planner not use the index...) --DD
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
