Re: [sqlite] How can I detect rows with non-ASCII values?

[Rocky Ji]

>
>  > GLOB supports character classes


thanks for teaching new keyword and its use.

My first attempt was very similar to what you suggest, except I used
sqlite3 and re from inside Python.

But as you see, I can't reliably seprate 'interrogative' question marks
from question marks that get displayed due to 'encoding faults'.

Any suggestions?

Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How can I detect rows with non-ASCII values?

[Rocky Ji]

Sorry for lack of clarity.

By question marks, I meant- that some text, like Dutch programmers names,
and address in Nordic locations, have accents and umaults and other such
modifications done to English-alphabets. These get displayed as ? or box

On Sat, Jan 18, 2020, 16:34 Clemens Ladisch  wrote:

> Rocky Ji wrote:
> > I am asked to highlight rows containing strange characters. All data were
> > ingested by a proprietary crawler.
> >
> > By strange, I mean, question marks, boxes, little Christmas Trees,  solid
> > arrows, etc. kind of symbols; these appear suddenly in flow of normal
> ASCII
> > English letters.
>
> GLOB supports character classes:
>
>   SELECT *
>   FROM MyTable
>   WHERE DataField GLOB '*[^ -~]*';
>
> Question marks _are_ ASCII characters.  If you want to allow fewer
> characters,
> list them:  [^ A-Za-z0-9,.-]
>
>
> Regards,
> Clemens
> ___
> 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] How can I detect rows with non-ASCII values?

[Rocky Ji]

Hi,

I am asked to highlight rows containing strange characters. All data were
ingested by a proprietary crawler.

By strange, I mean, question marks, boxes, little Christmas Trees,  solid
arrows, etc. kind of symbols; these appear suddenly in flow of normal ASCII
English letters.

How do I approach this?

Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is there a tool to convert `where`s to equivalent `join`s?

[Rocky Ji]

In SQL world, generally
,
not just SQLite
,
a lot  of
pros say that the constructs `from...inner join...on` and `from...where`
are equivalent
,
and that query optimizer should build identical plans for either variant.

Understanding `WHERE` is simple / straight-forward / intutive, to those
1. without mathematics background, or
2. used to imperative code and for-loops

So for learning sake, is there a tool that converts a query using `WHERE`
to a query (that yields identical results) using JOINs? Like a English ->
 side-by-side translator.

Cheers
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to refer to `this` table?

[Rocky Ji]

I went from

https://sqlite.org/lang_createtable.html

to

https://sqlite.org/syntax/table-constraint.html

to

https://sqlite.org/syntax/expr.html

and figured expr of `check` in table constraint may contain a nested select
after `not in`.

On Sat, Feb 23, 2019, 1:24 PM Clemens Ladisch  Rocky Ji wrote:
> > CREATE TABLE Aliases (
> >   alias_id INTEGER PRIMARY KEY AUTOINCREMENT,
> >   real_name TEXT NOT NULL,
> >   aka TEXT NOT NULL,
> >   CONSTRAINT xyz UNIQUE (real_name, aka),
> >   CONSTRAINT noCircularRef_A CHECK (
> > real_name NOT IN (SELECT aka FROM Aliases)
> >   ),
> >   CONSTRAINT noCircularRef_B CHECK (
> > aka NOT IN (SELECT real_name FROM Aliases)
> >   )
> > );
> >
> > Error: no such table: Aliases
>
> <https://www.sqlite.org/lang_createtable.html#ckconst> says:
> | The expression of a CHECK constraint may not contain a subquery.
>
> You'd have to write triggers to check this:
>
> CREATE TRIGGER noCircularRef_insert
> AFTER INSERT ON Aliases
> FOR EACH ROW
> WHEN NEW.real_name IN (SELECT aka FROM Aliases)
>   OR NEW.aka IN (SELECT real_name FROM Aliases)
> BEGIN
>   SELECT RAISE(FAIL, "circular reference");
> END;
> -- same for AFTER UPDATE OF real_name, aka
>
>
> Regards,
> Clemens
> ___
> 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

Re: [sqlite] What is the recommended way to write to views?

[Rocky Ji]

Thanks for the suggestion on `UNIQUE`, I should have done it. Based on this
new schema I wrote (pseudoSQL code):

create table dummy (d text);  -- empty table to let us use
case...when...then syntax
create table variables(
variable_id integer primary key autoincrement,
name text not null,
value integer not null
);

create trigger summary_insert
instead of insert on summary
begin
select *,
case when (select a_id from Alpha where a_attribute =
NEW.a_attribute) not null
then
(insert into variables (name, value) values ("AlphaPK", (select
a_id from Alpha where a_attribute = NEW.a_attribute)))
else
-- insert into Alpha
-- get last_insert_rowid
/*
nested case...when...then till all pks are attained
then insert into appropriate tables using something like
`select max(value) from variables where name = AlphaPK`
*/
from dummy;
end;


But I guess this is too ugly, and difficult to maintain. I'll use DAL to do
all this work and insert in tables directly.

On Wed, Feb 20, 2019 at 5:53 PM Simon Slavin  wrote:

> On 20 Feb 2019, at 11:14am, Rocky Ji  wrote:
>
> > create view summary
> > as
> >select
> >a.a_attribute,
> >b.b_attribute,
> >c.c_attribute
> >from
> >m2mAlphaBeta m
> >inner join Alpha a on a.a_id = m.FK_a_id
> >inner join Beta b on b.b_id = m.FK_b_id
> >inner join Charlie c on c.FK_C_a_id = a.a_id;
> >
> > And assuming all incoming data (say from CSV read) is correct. How do I
> write a record to `summary`?
>
> First, congratulations on getting "integer primary key autoincrement"
> right for keys which are FOREIGN KEY keys.  Common mistake.  However, I
> suspect you've missed a bet.  Consider
>
> > create table Alpha (
> >a_id integer primary key autoincrement,
> >a_attribute text
> > );
>
> Would "a_attribute TEXT UNIQUE" be better ?  This would guard against two
> entries in Alpha having the same attribute, which I think you wouldn't
> don't want.  You can do the same thing for the other _attribute columns.
>
> In answer to the question, "summary" is a VIEW.  You don't have to write
> anything to it.  It looks like it already does what you want.
>
> > Like how do I get ROWID of the "just inserted" record in A to insert
> into C properly. I am thinking "should I make transactions, but we don't
> have variables"... and going round-and-round without solution.
>
> Ah, I see.  Okay, I'm going to ignore Charlie for clarity.  You have a CSV
> file containing two columns: a.a_attribute and b.b_attribute, and you're
> wondering how to get the data into Alpha, Beta and m2mAlphaBeta.  The
> answer is ... not in one command.  It can't be done.
>
> Reading two values from your CSV file, theAAttrib and theBAttrib,
>
> First, make sure that Alpha and Beta have the rows they need.
>
> INSERT OR IGNORE INTO Alpha (a_attribute) VALUES (theAAttrib);
> INSERT OR IGNORE INTO Beta (b_attribute) VALUES (theBAttrib);
>
> But you don't know whether those created new rows or not, so you have to
> find the rows you're going to want to use:
>
> newA_id = result of "SELECT a_id FROM Alpha WHERE a_attribute = '<
> theAAttrib>'"
> newB_id = result of "SELECT b_id FROM BETA WHERE b_attribute = <
> theBAttrib>"
>
> Then you have the values you need for "INSERT INTO Charlie".
>
> Unforunately, you cannot use last_insert_rowid() because you do not know
> whether your "INSERT OR IGNORE" inserted a new row or not.
>
> Fortunately, your "UNIQUE" constraints on the attribute columns means that
> you have indexes on them.  This means that the "SELECT" commands will
> execute very quickly.
>
> Extend the above for Charlie.
>
> 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

Re: [sqlite] What is the recommended way to write to views?

[Rocky Ji]

Wow, that's some clever use. I tried doing it all in one go using a `vars`
table (name, value, timestamp)  and a `dummy` (empty table to enable access
to case syntax) table, made a mess of case...when...then... only to realize
that a then-expr can't contain insert clause. I guess I'll implement this
in DAL itself.

On Wed, Feb 20, 2019 at 9:15 PM David Raymond 
wrote:

> Well, you _can_ with this specific simplified schema, but it's a little
> sketchy and most likely will not scale. So if at all possible this sort of
> thing should be done from the controlling program rather than by a trigger.
> Also as mentioned, if the a/b/c _attribute fields aren't unique in the
> tables then it will also just result in duplication. So not the most
> useful. But hey, here's my solution for the over simplified version:
>
>
> As you said we don't have variables, so last_insert_rowid() can't get
> saved for each table.
>
> For this specific schema though the lack of "not null" on the foreign keys
> makes it doable, as long as "not null" is the actual expectation and
> nothing in the m2m table stays around with a null.
>
>
> create trigger trg_summary_insert
> instead of insert on summary
> for each row
> begin
>
> insert into Beta (b_attribute) values (new.b_attribute);
>
> insert into m2mAlphaBeta (FK_b_id) select last_insert_rowid();
> --This leaves FK_a_id null.
>
> insert into Alpha (a_attribute) values (new.a_attribute);
>
> update m2mAlphaBeta set FK_a_id = last_insert_rowid() where FK_a_id is
> null;
> --Only the new row inserted above should have a null, so the new row gets
> updated
> --without us needing to know the rowid for the m2m table.
>
> --And since the previous statement was an update,
> --then last_insert_rowid() will keep the value from the Alpha insert.
> insert into Charlie (c_attribute, FK_C_a_id) values (new.c_attribute,
> last_insert_rowid());
>
> end;
>
>
> sqlite> insert into summary values ('A', 1.0, 'One'), ('B', 2.2, 'Two');
>
> sqlite> select * from summary;
> a_attribute|b_attribute|c_attribute
> A|1.0|One
> B|2.2|Two
>
> sqlite> select * from Alpha;
> a_id|a_attribute
> 1|A
> 2|B
>
> sqlite> select * from Beta;
> b_id|b_attribute
> 1|1.0
> 2|2.2
>
> sqlite> select * from Charlie;
> c_id|c_attribute|FK_C_a_id
> 1|One|1
> 2|Two|2
>
> sqlite> select * from m2mAlphaBeta;
> _id|FK_a_id|FK_b_id
> 1|1|1
> 2|2|2
>
> --Throw in junk values to make sure it's not just because of coincidence
> that the new rowid's are the same for each table
>
> sqlite> insert into Alpha values (17, 'Seventeen');
>
> sqlite> insert into Beta values (32, 32.0);
>
> sqlite> insert into Charlie values (57, 'Fifty-Seven', null);
>
> --And try it again
> sqlite> insert into summary values ('C', 3.3, 'Three');
>
> sqlite> select * from summary;
> a_attribute|b_attribute|c_attribute
> A|1.0|One
> B|2.2|Two
> C|3.3|Three
>
> --However, this just blindly throws in duplicates since there're no unique
> constraints
>
> sqlite> insert into summary values ('C', 3.3, 'Three');
>
> sqlite> select * from summary;
> a_attribute|b_attribute|c_attribute
> A|1.0|One
> B|2.2|Two
> C|3.3|Three
> C|3.3|Three
>
> sqlite>
>
>
> Also at the very end here I realize this also requires no triggers on
> inserts for any of the real tables. Otherwize last_insert_rowid() will be
> giving results from the inserts in the recursive triggers, and throw things
> off.
>
> So yes, if possible do it in the controlling program rather than in
> triggers :)
>
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Rocky Ji
> Sent: Wednesday, February 20, 2019 6:14 AM
> To: SQLite mailing list
> Subject: [sqlite] What is the recommended way to write to views?
>
> Hi everyone,
>
> I know the simple answer to be `instead of insert ...`. But consider a
> situation where:
>
> Alpha has-many Beta,and
> Beta has-many Alpha
> Alpha has-many Charlie, while
> Charlie has one Alpha
>
> So if my SQL looks like:
> -- PRAGMA fk ON;
> create table Alpha (
> a_id integer primary key autoincrement,
> a_attribute text
> );
>
> create table Beta (
> b_id integer primary key autoincrement,
> b_attribute real
> );
>
> create table Charlie (
> c_id integer primary key autoincrement,
> c_attribute text,
> FK_C_a_id integer,
> constraint 

[sqlite] How to refer to `this` table?

[Rocky Ji]

If I do

CREATE TABLE Sample (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  parent_id INTEGER,
  CONSTRAINT p FOREIGN KEY (parent_id) REFERENCES Sample (id)
);

I don't get any errors and the schema behaves as expected. But if I try

CREATE TABLE Aliases (
  alias_id INTEGER PRIMARY KEY AUTOINCREMENT,
  real_name TEXT NOT NULL,
  aka TEXT NOT NULL,
  CONSTRAINT xyz UNIQUE (real_name, aka),
  CONSTRAINT noCircularRef_A CHECK (
real_name NOT IN (SELECT aka FROM Aliases)
  ),
  CONSTRAINT noCircularRef_B CHECK (
aka NOT IN (SELECT real_name FROM Aliases)
  )
);

I am getting an `Error: no such table: Aliases` error. So how do I
implement this constraint? Are there any special keywords, like NEW and OLD
of trigger statements, to refer to current table?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] What is the recommended way to write to views?

[Rocky Ji]

Hi everyone,

I know the simple answer to be `instead of insert ...`. But consider a
situation where:

Alpha has-many Beta,and
Beta has-many Alpha
Alpha has-many Charlie, while
Charlie has one Alpha

So if my SQL looks like:
-- PRAGMA fk ON;
create table Alpha (
a_id integer primary key autoincrement,
a_attribute text
);

create table Beta (
b_id integer primary key autoincrement,
b_attribute real
);

create table Charlie (
c_id integer primary key autoincrement,
c_attribute text,
FK_C_a_id integer,
constraint abc foreign key (FK_C_a_id) references Alpha (a_id)
);

create table m2mAlphaBeta (
_id integer primary key autoincrement,
FK_a_id integer,
FK_b_id integer,
constraint def foreign key (FK_a_id) references Alpha (a_id),
constraint ghi foreign key (FK_b_id) references Beta (b_id)
);


create view summary
as
select
a.a_attribute,
b.b_attribute,
c.c_attribute
from
m2mAlphaBeta m
inner join Alpha a on a.a_id = m.FK_a_id
inner join Beta b on b.b_id = m.FK_b_id
inner join Charlie c on c.FK_C_a_id = a.a_id
;


And assuming all incoming data (say from CSV read) is correct. How do I
write a record to `summary`?

Like how do I get ROWID of the "just inserted" record in A to insert into C
properly. I am thinking "should I make transactions, but we don't have
variables"... and going round-and-round without solution.

Please recommend the canonical way to write to such views.

Good day.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to get aggregate without reducing number of rows (repeats are ok)?

[Rocky Ji]

Whoa! Big revelation. I didn't know foreign keys were disabled by default.
And my code just ran in sqlite3 shell, and this made me think text and
rowid and etc foreign keys "just worked".

Sorry for the ruckus.

Here's the new thing: https://pastebin.com/raw/pSqjvJdZ

Again, can we get rid of them sub-query?

Be nice.

On Mon, Feb 18, 2019, 3:22 PM R Smith  On 2019/02/18 11:24 AM, Clemens Ladisch wrote:
> > Rocky Ji wrote:
> >> But everyone advices against nested select statements.
> > Who?
> >
> > I've heard rumors that older version of the Oracle query optimizer did
> > worse with subqueries than with joins, but such advice is not necessarily
> > correct for SQLite.
>
> +1
>
> Whomever said to avoid nested or sub queries are lying to you - it's
> like saying "Use only left turns when driving, avoid right turns" - it's
> just silly, they both help to get you there.
>
> That said, when you can achieve a result using only an outer query or a
> join that may utilize an index, then sure, you should prefer that over a
> sub query for efficiency purposes, but that in no way means to "avoid"
> them completely. They are part and parcel of SQL and very much optimized
> for (in modern DB engines) and often work faster and better.
>
> Your advisors may have meant a specific older engine that had known
> issues with sub-selects or such.
>
> I see you telling Keith that the schema wording doesn't matter and that
> the question is hypothetical, but have you actually run the schema you
> made? It doesn't work because, as Keith pointed out, those are invalid
> foreign keys. (It only runs if you set PRAGMA foreign_keys = 0; but that
> negates the purpose, it should be on).
>
> Remake the schema, use ABCD if that suits you better, but at least make
> a schema that works, and restate the question. That way we can run the
> schema on our side, compose the queries that would answer your question
> without us having to spend half an hour first rewriting the schema into
> a working one (which then may well destroy the premise of your question).
>
> Cheers,
> Ryan
>
> ___
> 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

Re: [sqlite] How to get aggregate without reducing number of rows (repeats are ok)?

[Rocky Ji]

sible
> (there is no point in including the "matches" table twice, for example, and
> the correlated subquery could be put right in the main query, and if the
> database schema itself were normalized, then even further simplifications
> would be possible).
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Rocky Ji
> >Sent: Sunday, 17 February, 2019 20:19
> >To: SQLite mailing list
> >Subject: Re: [sqlite] How to get aggregate without reducing number of
> >rows (repeats are ok)?
> >
> >@Keith
> >
> >Thanks. I am new to SQL and DB in general; please clarify what *is it
> >valid* means. How do I check validity of schema?
> >
> >On Mon, Feb 18, 2019, 1:17 AM Keith Medcalf  >wrote:
> >
> >>
> >> Nice schema.  Do you have a valid one?
> >>
> >>
> >> ---
> >> The fact that there's a Highway to Hell but only a Stairway to
> >Heaven says
> >> a lot about anticipated traffic volume.
> >>
> >>
> >> >-Original Message-
> >> >From: sqlite-users [mailto:sqlite-users-
> >> >boun...@mailinglists.sqlite.org] On Behalf Of Rocky Ji
> >> >Sent: Sunday, 17 February, 2019 09:47
> >> >To: SQLite mailing list
> >> >Subject: [sqlite] How to get aggregate without reducing number of
> >> >rows (repeats are ok)?
> >> >
> >> >Hello everyone,
> >> >
> >> >How can I prevent group by clause from reducing the number of rows
> >> >without
> >> >affecting accuracy of what aggregate functions provide?
> >> >
> >> >Scenario:
> >> >My club has-many coaches.
> >> >Each coach trains a team of players.
> >> >Of course, a player has-many matches and a match has-many players.
> >> >Given the schema: https://pastebin.com/raw/C77mXsHJ
> >> >and sample data: https://pastebin.com/raw/GhsYktRS
> >> >
> >> >I want a result like: https://pastebin.com/raw/stikDvYS
> >> >
> >> >NOTE: for a match X, profit/match is `sum(salary of all players
> >> >playing in
> >> >X) - X.bets`
> >> >
> >> >To get the result, here's what I came up with:
> >> >https://pastebin.com/ckgicBWS
> >> >
> >> >If I un-comment those lines, I get the profit column but rows are
> >> >reduced,
> >> >how can I prevent that?
> >> >
> >> >Thanks,
> >> >Rocky.
> >> >___
> >> >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
>
>
>
> ___
> 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

Re: [sqlite] How to get aggregate without reducing number of rows (repeats are ok)?

[Rocky Ji]

@Keith

Thanks. I am new to SQL and DB in general; please clarify what *is it
valid* means. How do I check validity of schema?

On Mon, Feb 18, 2019, 1:17 AM Keith Medcalf 
> Nice schema.  Do you have a valid one?
>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Rocky Ji
> >Sent: Sunday, 17 February, 2019 09:47
> >To: SQLite mailing list
> >Subject: [sqlite] How to get aggregate without reducing number of
> >rows (repeats are ok)?
> >
> >Hello everyone,
> >
> >How can I prevent group by clause from reducing the number of rows
> >without
> >affecting accuracy of what aggregate functions provide?
> >
> >Scenario:
> >My club has-many coaches.
> >Each coach trains a team of players.
> >Of course, a player has-many matches and a match has-many players.
> >Given the schema: https://pastebin.com/raw/C77mXsHJ
> >and sample data: https://pastebin.com/raw/GhsYktRS
> >
> >I want a result like: https://pastebin.com/raw/stikDvYS
> >
> >NOTE: for a match X, profit/match is `sum(salary of all players
> >playing in
> >X) - X.bets`
> >
> >To get the result, here's what I came up with:
> >https://pastebin.com/ckgicBWS
> >
> >If I un-comment those lines, I get the profit column but rows are
> >reduced,
> >how can I prevent that?
> >
> >Thanks,
> >Rocky.
> >___
> >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

Re: [sqlite] How to get aggregate without reducing number of rows (repeats are ok)?

[Rocky Ji]

@Luuk

that was my initial approach. But everyone advices against nested select
statements. Can we do it without that sub-query?

On Sun, Feb 17, 2019, 11:04 PM Luuk 
> On 17-2-2019 17:46, Rocky Ji wrote:
> > Hello everyone,
> >
> > How can I prevent group by clause from reducing the number of rows
> without
> > affecting accuracy of what aggregate functions provide?
> >
> > Scenario:
> > My club has-many coaches.
> > Each coach trains a team of players.
> > Of course, a player has-many matches and a match has-many players.
> > Given the schema: https://pastebin.com/raw/C77mXsHJ
> > and sample data: https://pastebin.com/raw/GhsYktRS
> >
> > I want a result like: https://pastebin.com/raw/stikDvYS
> >
> > NOTE: for a match X, profit/match is `sum(salary of all players playing
> in
> > X) - X.bets`
> >
> > To get the result, here's what I came up with:
> https://pastebin.com/ckgicBWS
> >
> > If I un-comment those lines, I get the profit column but rows are
> reduced,
> > how can I prevent that?
> >
> > Thanks,
> > Rocky.
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> C:\TEMP>\util\sqlite3
> SQLite version 3.27.1 2019-02-08 13:17:39
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table test(i int, tekst varchar(20));
> sqlite> insert into test values (1,'a');
> sqlite> insert into test values (2,'b');
> sqlite> insert into test values (3,'c');
> sqlite> insert into test values (4,'a');
> sqlite> select t1.i, t1.tekst, (select sum(t2.i) from test t2 where
> t2.tekst=t1.tekst) as totals from test t1;
> 1|a|5
> 2|b|2
> 3|c|3
> 4|a|5
> sqlite>
>
> ___
> 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] How to get aggregate without reducing number of rows (repeats are ok)?

[Rocky Ji]

Hello everyone,

How can I prevent group by clause from reducing the number of rows without
affecting accuracy of what aggregate functions provide?

Scenario:
My club has-many coaches.
Each coach trains a team of players.
Of course, a player has-many matches and a match has-many players.
Given the schema: https://pastebin.com/raw/C77mXsHJ
and sample data: https://pastebin.com/raw/GhsYktRS

I want a result like: https://pastebin.com/raw/stikDvYS

NOTE: for a match X, profit/match is `sum(salary of all players playing in
X) - X.bets`

To get the result, here's what I came up with: https://pastebin.com/ckgicBWS

If I un-comment those lines, I get the profit column but rows are reduced,
how can I prevent that?

Thanks,
Rocky.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to get all SQL statement syntax images?

[Rocky Ji]

I found the solutions to my issue, thanks everyone. How do I mark this
thread [SOLVED]

On Mon, Sep 3, 2018, 7:21 AM Warren Young  wrote:

> On Sep 2, 2018, at 4:15 AM, Richard Hipp  wrote:
> >
> > On 9/1/18, Rocky Ji  wrote:
> >> Hi everyone,
> >>
> >> (Mailing list newbie here). Where can I get a corpus of all the
> >> -stmt.gif that are shown in the online docs e.g.
> >> https://sqlite.org/images/syntax/insert-stmt.gif ?
> >
> > https://www.sqlite.org/syntaxdiagrams.html
>
> If you just want the GIF files, clone the docsrc repo, which when opened
> gives you art/syntax/*.gif:
>
> https://www.sqlite.org/docsrc/
> ___
> 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

Re: [sqlite] How to get all SQL statement syntax images?

[Rocky Ji]

Wow! That is an amazing compilation, thanks.

P.S. I am a big fan of your work.

On Sun, Sep 2, 2018, 3:45 PM Richard Hipp  wrote:

> On 9/1/18, Rocky Ji  wrote:
> > Hi everyone,
> >
> > (Mailing list newbie here). Where can I get a corpus of all the
> > -stmt.gif that are shown in the online docs e.g.
> > https://sqlite.org/images/syntax/insert-stmt.gif ?
>
> https://www.sqlite.org/syntaxdiagrams.html
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] How to get all SQL statement syntax images?

[Rocky Ji]

Hi everyone,

(Mailing list newbie here). Where can I get a corpus of all the
-stmt.gif that are shown in the online docs e.g.
https://sqlite.org/images/syntax/insert-stmt.gif ?

I tried using Google like - * filetype:gif site:sqlite.org/images/syntax/
but that didn't work.

So how do I access all these syntax explainer images?

Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users