That is the one and only whole statement, and I see no references to anything
else in the initial query.
So, delete
with
kcounts(t, c) as (
select code_type, count(*)
from keys
group by code_type
),
...
select ...,
(select c from kcounts where t = 'SET') as "#sets",
(select c from kcounts where t = 'CST') as "#constants",
(select c from kcounts where t = 'CMT') as "#comments",
(select c from kcounts where t = 'LOG') as "#logs",
...;
in its entirety and replace with
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;
if you were doing something like
select sum(code_type == 'SET') as sets,
....
sum(some other field == 'VAL') as somethingelse
from keys, someothertable
where keys.correlatingfield = somothertable.correlatingfield;
then you can do that.
So if you problem statement is "Project the following tables (FROM clause) on
the following conditions (WHERE clause) counting the occurrences of something
(sum(something=='whatitequals')).
Oftentimes a CTE is a cute but non-productive (ie, highly inefficient) way of
phrasing a query if that query does not require recursion.
--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
> -----Original Message-----
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Dominique Devienne
> Sent: Thursday, 6 July, 2017 08:18
> To: SQLite mailing list
> Subject: Re: [sqlite] Any way to avoid scanning a large table several
> times?
>
> 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."
> _______________________________________________
> 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
