select wonkys, fubars, sets, constants, comments, logs, whipitys, doodas
from (select sum(wonkies == 'WNK') as wonkys,
sum(fubars == 'FBR') as fubars
from table_to_summarize
) as S1,
(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
) as S2,
(select sum(whipity == 'WHP') as whippitys,
sum(dooda == 'DOO') as doodas
from another_table_to_get_statistics_from
) as S3;
Get statistics from multiple tables with only one table scan of each table.
For more than one table, simply extend by adding join queries to the from
clause. Each join table (in the FROM clause) must return exactly one row.
Note that the outer select can be a select * if your application can find the
columns by name ...
--
˙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
