Any way to avoid scanning a large table several times?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
15 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Any way to avoid scanning a large table several times?

Dominique Devienne
I have a view gathering statistics from 3 different tables, one of which is
largish (~ 2M rows).

The view is similar to this:

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",
       ...;

With all 4 counts, the views takes ~ 6.5s to compute its 1 row (with the
file in cache, was 13.5s with a cold filesystem cache, while DB is only
380MB in size).

If I remove 3 of the lines, keeping a single "select c from kcounts where t
= ..." that drops to 2.15s, or about one third. (this is inside SQLiteSpy
using SQLite 3.13.0 BTW).

As a programmer, I can see this can be done in a single pass, but most
likely the SQL as written does several passes over kcounts, despite using a
CTE (I had hoped kcounts would be instantiated into an ephemeral table, and
thus accessing a 4 row tables would be "free").

An explain query plan indeed shows 4x "SCAN TABLE keys" steps, not just 1.

Is there a way to reformulate this query differently to somehow achieve a
single pass on the keys table? Or force the query planner to "instantiate"
the kcounts "CTE view" to achieve that single scan, via some kind of hint?
If none exists, could one such hint be added? Ideally the planner itself
would decide kcounts should be instantiated, but I'm happy to tell him it
should via a hint.

The speed up is non-negligible at 3x, thus my request for comments from
experts on this.

Thanks, --DD

PS: BTW, I did run ANALYZE and that didn't change the plan or execution
time.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Any way to avoid scanning a large table several times?

Simon Slavin-3


On 6 Jul 2017, at 9:52am, Dominique Devienne <[hidden email]> wrote:

> With all 4 counts, the views takes ~ 6.5s to compute its 1 row

Just checking ….

Do you have an index on keys(code_type) ?

Would it not be possible to express this VIEW without the "WITH" construction ?  For instance if there is a "SETS" row for every row you want in the VIEW, you could just use

SELECT … FROM keys AS kSet
        JOIN keys AS Cst WHERE Cst.code_type = kSet.code_type
        JOIN …
        JOIN …
        WHERE code_type = "SETS"

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Any way to avoid scanning a large table several times?

Clemens Ladisch
In reply to this post by Dominique Devienne
Dominique Devienne wrote:

> 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",
>        ...;
>
> the SQL as written does several passes over kcounts

However, the subquery is flattened: each pass counts only rows with
a specific code_type value.

An index on code_type would help _very much_.

> Is there a way to reformulate this query differently to somehow achieve a
> single pass on the keys table? Or force the query planner to "instantiate"
> the kcounts "CTE view" to achieve that single scan, via some kind of hint?

It would be possible to prevent the subquery flattening
(http://www.sqlite.org/optoverview.html#flattening), but AFAIK there is
no hint that would force SQLite to recognize that all the instances of
kcounts are then identical and can be reused.

You could put kcounts into a temporary table.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Any way to avoid scanning a large table several times?

Dominique Devienne
On Thu, Jul 6, 2017 at 12:54 PM, Clemens Ladisch <[hidden email]> wrote:

> Dominique Devienne wrote:
> > 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",
> >        ...;
> >
> > the SQL as written does several passes over kcounts
>
> However, the subquery is flattened: each pass counts only rows with
> a specific code_type value.
>
> An index on code_type would help _very much_.


Actually not that much apparently.
No Simon, I didn't have an index on code_type.
In fact keys is itself a view, and cote_type a case expression.

That's fine, I created a expression index using

create index idx_keys_code on keys_tab(
       case
       when (code & 1) = 1 then 'SET'
       when (code & 2) = 2 then 'CST'
       when (code & 4) = 4 then 'CMT'
       when (code & 8) = 8 then 'LOG'
       else null
       end
)

The plan correctly changes to "SCAN TABLE keys USING INDEX idx_keys_code",
4x.

And the runtime only drops to ~5.4s closer to 6.5s than 2.15s,
but at the cost of 22MB extra in the DB file for the index. Not sure that a
good tradeof.

> Is there a way to reformulate this query differently to somehow achieve a
> > single pass on the keys table? Or force the query planner to
> "instantiate"
> > the kcounts "CTE view" to achieve that single scan, via some kind of
> hint?
>
> It would be possible to prevent the subquery flattening
> (http://www.sqlite.org/optoverview.html#flattening), but AFAIK there is
> no hint that would force SQLite to recognize that all the instances of
> kcounts are then identical and can be reused.
>
> You could put kcounts into a temporary table.
>

I could it in a table, but then who updates kcounts when keys (or keys_tab)
changes?
I guess one can play with triggers then, to emulate materialized views,
opening a new can of worm.

These are "persistent report views", as already discussed when the thousand
separator was added to printf.
"Users" just "view" these views in any SQLite clients, typically a GUI one.
And they should remain "dynamic"
into case the table contents are modified with DMLs.

Maybe the "hint" to create an ephemeral table for a "CTE view" could be to
prefix the CTE view name with temp. ?

I know that Richard/SQLite are not much into planner hints, but a few
exists already.

What do you think Richard? --DD

> with
> temp.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",
>        ...;
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Any way to avoid scanning a large table several times?

Simon Slavin-3


On 6 Jul 2017, at 12:32pm, Dominique Devienne <[hidden email]> wrote:

> Actually not that much apparently.
> No Simon, I didn't have an index on code_type.
> In fact keys is itself a view, and cote_type a case expression.

The index you created is not helpful.  It is not useful for what you’re doing.

I suggest you remove the VIEW layer from your layered query, then reconsider what indexes might be helpful.  We can advise you on indexes if you post the resulting query.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Any way to avoid scanning a large table several times?

Clemens Ladisch
In reply to this post by Dominique Devienne
Dominique Devienne wrote:
> On Thu, Jul 6, 2017 at 12:54 PM, Clemens Ladisch wrote:
>> You could put kcounts into a temporary table.
>
> I could it in a table, but then who updates kcounts when keys (or keys_tab)
> changes?

I did not say "table" but "temporary table":

BEGIN;
CREATE TEMPORARY TABLE kcounts AS SELECT ...;
SELECT ... FROM kcounts ...;
DROP TABLE kcounts;
COMMIT;

> "Users" just "view" these views in any SQLite clients, typically a GUI one.
> And they should remain "dynamic"
> into case the table contents are modified with DMLs.

SQLite is designed as an embedded database, and expects that additional
logic, if needed, can be provided by the application.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Any way to avoid scanning a large table several times?

Dominique Devienne
In reply to this post by Simon Slavin-3
On Thu, Jul 6, 2017 at 2:49 PM, Simon Slavin <[hidden email]> wrote:

> On 6 Jul 2017, at 12:32pm, Dominique Devienne <[hidden email]> wrote:
> > Actually not that much apparently.
> > No Simon, I didn't have an index on code_type.
> > In fact keys is itself a view, and cote_type a case expression.
>
> The index you created is not helpful.  It is not useful for what you’re
> doing.
>

You what evidence do you get that from?

The new plan uses the index though, as I previously indicated, so I'm
surprised you'd say that.

It's a group-by query, so despite using the index, all rowids for the only
4 different "index entries" must still be counted,
and that's still definitely longer to do that than full scanning the table
once.


> I suggest you remove the VIEW layer from your layered query, then
> reconsider what indexes might be helpful.  We can advise you on indexes if
> you post the resulting query.


I don't see how that would make the query any faster. The plan already
shows it scan the "real" table, not the intermediate view (w/o the index
that is). --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Any way to avoid scanning a large table several times?

Clemens Ladisch
Dominique Devienne wrote:
> It's a group-by query, so despite using the index, all rowids for the only
> 4 different "index entries" must still be counted,
> and that's still definitely longer to do that than full scanning the table
> once.

So why using GROUP BY?  The top-level query does not really want all
groups, it wants to determine individual counts.

I estimate that a simple "SELECT count(*) FROM tab WHERE code_type = ?"
would be faster.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Any way to avoid scanning a large table several times?

Dominique Devienne
In reply to this post by Clemens Ladisch
On Thu, Jul 6, 2017 at 3:50 PM, Clemens Ladisch <[hidden email]> wrote:

> Dominique Devienne wrote:
> > On Thu, Jul 6, 2017 at 12:54 PM, Clemens Ladisch wrote:
> >> You could put kcounts into a temporary table.
> >
> > I could it in a table, but then who updates kcounts when keys (or
> keys_tab)
> > changes?
>
> I did not say "table" but "temporary table":
>
> BEGIN;
> CREATE TEMPORARY TABLE kcounts AS SELECT ...;
> SELECT ... FROM kcounts ...;
> DROP TABLE kcounts;
> COMMIT;
>
> > "Users" just "view" these views in any SQLite clients, typically a GUI
> one.
> > And they should remain "dynamic"
> > into case the table contents are modified with DMLs.
>
> SQLite is designed as an embedded database, and expects that additional
> logic, if needed, can be provided by the application.


I never fail to be surprised when every time SQLite doesn't do something
one can reasonably
expect it would do, people always reach for the "lite" or "embedded"
arguments...

Just

  select code_type, count(*)
   from well_keys
  group by code_type

by itself takes 2.01s. Accessing the other two tables takes < 200ms for
both.

The main select itself is just formatting, except of course it "transposes"
the 4 rows from kcounts as columns.
The fact SQLite flattens the kcounts CTE view is in fact "the problem" is
this case. Should it instead "materialize"
it in an "ephemeral table", the query would be 3x faster (Nx faster is not
4 rows, but N+1 rows in fact).

As Richard often points out, there's no bug here, just an optimization
opportunity. The result is correct.

But saying one should resort to temporary tables to work-around a
non-optimization and thus be forced
to abandon nice simple views because SQLite is an "embedded" database is
just "meh", for lack of a better word :) --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Any way to avoid scanning a large table several times?

Keith Medcalf
In reply to this post by Dominique Devienne

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 ...

--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı

> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of Dominique Devienne
> Sent: Thursday, 6 July, 2017 02:52
> To: General Discussion of SQLite Database
> Subject: [sqlite] Any way to avoid scanning a large table several times?
>
> I have a view gathering statistics from 3 different tables, one of which
> is
> largish (~ 2M rows).
>
> The view is similar to this:
>
> 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",
>        ...;
>
> With all 4 counts, the views takes ~ 6.5s to compute its 1 row (with the
> file in cache, was 13.5s with a cold filesystem cache, while DB is only
> 380MB in size).
>
> If I remove 3 of the lines, keeping a single "select c from kcounts where
> t
> = ..." that drops to 2.15s, or about one third. (this is inside SQLiteSpy
> using SQLite 3.13.0 BTW).
>
> As a programmer, I can see this can be done in a single pass, but most
> likely the SQL as written does several passes over kcounts, despite using
> a
> CTE (I had hoped kcounts would be instantiated into an ephemeral table,
> and
> thus accessing a 4 row tables would be "free").
>
> An explain query plan indeed shows 4x "SCAN TABLE keys" steps, not just 1.
>
> Is there a way to reformulate this query differently to somehow achieve a
> single pass on the keys table? Or force the query planner to "instantiate"
> the kcounts "CTE view" to achieve that single scan, via some kind of hint?
> If none exists, could one such hint be added? Ideally the planner itself
> would decide kcounts should be instantiated, but I'm happy to tell him it
> should via a hint.
>
> The speed up is non-negligible at 3x, thus my request for comments from
> experts on this.
>
> Thanks, --DD
>
> PS: BTW, I did run ANALYZE and that didn't change the plan or execution
> time.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Any way to avoid scanning a large table several times?

Dominique Devienne
In reply to this post by Clemens Ladisch
On Thu, Jul 6, 2017 at 3:56 PM, Clemens Ladisch <[hidden email]> wrote:

> Dominique Devienne wrote:
> > It's a group-by query, so despite using the index, all rowids for the
> only
> > 4 different "index entries" must still be counted,
> > and that's still definitely longer to do that than full scanning the
> table
> > once.
>
> So why using GROUP BY?  The top-level query does not really want all
> groups, it wants to determine individual counts.
>
> I estimate that a simple "SELECT count(*) FROM tab WHERE code_type = ?"
> would be faster.
>

And you'd be right! Thanks a bunch. That's helpful.

704ms with the index, 3,010ms w/o it. The latter is still > 2x faster w/o
the 22MB index penalty.

       (select count(*) from well_keys where code_type = 'SET') as "#sets",

       (select count(*) from well_keys where code_type = 'CST') as
"#constants",
       (select count(*) from well_keys where code_type = 'CMT') as
"#comments",
       (select count(*) from well_keys where code_type = 'LOG') as "#logs",


Now though, I'm intrigued as to why the group-by is so much slower than the
"unrolled" count(*)-where... --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Any way to avoid scanning a large table several times?

Dominique Devienne
In reply to this post by Keith Medcalf
On Thu, Jul 6, 2017 at 4:07 PM, Keith Medcalf <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Any way to avoid scanning a large table several times?

Dominique Devienne
On Thu, Jul 6, 2017 at 4:17 PM, Dominique Devienne <[hidden email]>
wrote:

> On Thu, Jul 6, 2017 at 4:07 PM, Keith Medcalf <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Any way to avoid scanning a large table several times?

Keith Medcalf
In reply to this post by Dominique Devienne

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:[hidden email]]
> 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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Any way to avoid scanning a large table several times?

Keith Medcalf
In reply to this post by Dominique Devienne

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:[hidden email]]
> 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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...