Re: [sqlite] Strange behavior with sum
On Thu, 01 Jun 2006 23:08:13 -0500, Kareem Badr <[EMAIL PROTECTED]> wrote: > Does anyone know what's going on here? Is this just user error? It's just integer division. D:\sqlite>sqlite3 :memory: SQLite version 3.3.4 Enter ".help" for instructions sqlite> create table bob (f1 varchar, f2 varchar); sqlite> insert into bob values (206140624896,237003182080); sqlite> select sum(f1),sum(f2) from bob; 206140624896|237003182080 sqlite> select sum(f1)/sum(f2) from bob; 0 sqlite> select cast(sum(f1) as double)/cast(sum(f2) as double) from bob; 0.869779988128673 sqlite> klint. +---+-+ : Klint Gore: "Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+
Re: [sqlite] Unsupported SQL feature
On Sun, 06 Nov 2005 15:09:34 -0500, [EMAIL PROTECTED] wrote: > Alexander Kozlovsky <[EMAIL PROTECTED]> wrote: > > I just notice, SQLite don't support this type of queries: > > > > > > select * from t1 where a >= ALL (select b from t2) > > > > > > Of course, queries with ALL, ANY and SOME clauses may be reformulated, > > but this is part of SQL standard. > > What are these constructs suppose to do? [from the sql 1992 standard] 8.7 Function Specify a quantified comparison. Format ::= ::= | ::= ALL ::= SOME | ANY Syntax Rules 1) The shall be of the same degree as the result of the . 2) The data types of the values of the shall be respectively comparable to those of the columns of the . 3) The collating sequence for each pair of respective values in the is determined in the same manner as described in Subclause 8.2, "". Access Rules None. General Rules 1) Let R be the result of the and let T be the result of the . 2) The result of "R T" is derived by the application of the implied "R RT" to every row RT in T: Case: a) If T is empty or if the implied is true for every row RT in T, then "R T" is true. b) If the implied is false for at least one row RT in T, then "R T" is false. c) If the implied is true for at least one row RT in T, then "R T" is true. d) If T is empty or if the implied is false for every row RT in T, then "R T" is false. e) If "R T" is neither true nor false, then it is unknown. +---+-+ : Klint Gore: "Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+
Re: AW: [sqlite] and , or
On Wed, 19 Oct 2005 09:59:49 +0200, "Eggert, Henri" <[EMAIL PROTECTED]> wrote: > What I need is a select statement which returns both. try select Id from Data where (coalesce(text,'') = '') and (coalesce(comming,'') = ''); klint. +---+-----+ : Klint Gore: "Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+
Re: [sqlite] Upcoming Conferences
On Mon, 10 Oct 2005 07:12:40 -0700 (PDT), Dan Kennedy <[EMAIL PROTECTED]> wrote: > > > --- [EMAIL PROTECTED] wrote: > > > "Gerald Dachs" <[EMAIL PROTECTED]> wrote: > > > > 2005-11-08 Frankfort, Germany > > > > > > I guess you mean Frankfurt ;). Be aware that the are two cities in Germany > > > that are named Frankfurt. I expect that the Conference will be in > > > Frankfurt > > > am Main and not in Frankfurt an der Oder. > > > > > > > Yes. I appologize for the misspelling. And, No, I was not > > aware of the second Frankfurt. Frankfurt am Main is the only > > one I had ever heard of. > > How about "Sidney" then? We're offended too! :) It can't be any worse than what's his name anouncing the sydney olympics. At $600, I don't think I'll be attending the tutorial no matter how Sydney is spelt. http://www.auug.org.au/events/2005/auug2005/AUUG-2005-Conf-Brochure.pdf klint. +---+-+ : Klint Gore: "Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+
Re: [sqlite] SUM and NULL values
On Thu, 08 Sep 2005 16:24:14 -0400, "D. Richard Hipp" <[EMAIL PROTECTED]> wrote:
> Can somebody come up with a realistic scenario where they would
> actually want SUM() to return NULL instead of 0? NULL is such
> a profoundly broken return value for sum() in my mind that I'm
> thinking of ignoring the standard and just coding SQLite to do
> the Right Thing. But I am open to the possibility that there
> are some cases outside of my imagination where returning zero
> might be considered "wrong".
Isn't the meaning of 0 part of the application logic? If you really are
going to do it, can there be a pragma to switch the behaviour?
> If nobody can suggest a scenario where SUM() returning NULL is
> actually helpful, then I will likely return SQLite to its former
> mode of operation which is to have SUM() return 0 when it has no
> input.
What about when the sum is on a field in a left joined table?
create table items
(item_code char(10), description varchar(30));
insert into items values('WIDGETS','WIDGET 3.5 inch');
insert into items values('BOXES','Box 2 inch');
insert into items values('ETCHA','Etch-a-sketch');
create table transactions
(item_code char(10),
customer_code char(10),
purchase_date date,
amt numeric);
-- customer buys widget
insert into transactions('WIDGETS','CASH','20050905',11.00);
-- customer brings widget back
insert into transactions('WIDGETS','CASH','20050907,'-11.00);
-- customer from last month brings box back
insert into transactions('BOXES','CASH','20050907,'-20.00);
-- monthly profitiblity report
select items.item_code,
sum(transactions.amt), count (transactions.item_code)
>from items
left outer join transactions on transactions.item_code = items.item_code
where date >= '20050901' and date <= '20050930'
order by 3 desc, 4 desc
I want the result to be descending money, then descending counts, with
no activity items at the end.
klint.
+---+-+
: Klint Gore: "Non rhyming:
: EMail : [EMAIL PROTECTED] : slang - the:
: Snail : A.B.R.I.: possibilities :
: Mail University of New England : are useless" :
: Armidale NSW 2351 Australia : L.J.J. :
: Fax : +61 2 6772 5376 : :
+---+-+
[sqlite] Did OSCON tutorial get accepted for AUUG in oct?
Anyone know if the OSCON tutorial was accepted for AUUG2005? klint. +---+-+ : Klint Gore: "Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+
Re: [sqlite] Re: - Re: [sqlite] Training opportunity: The Inner Workings Of SQLite
On Mon, 20 Jun 2005 10:34:05 +1000, [EMAIL PROTECTED] wrote: > I too would be happy to pay for DVDs if they were to be made available. > Tyranny of distance prevents me from attending (Sydney, Australia based). > > rayB At least you get staff discount and are already in sydney :-( ARM to SYD ~$300 SYD to LA ~$2000 LA to OR $??? fingerprinted at the airport for going to a 3hr tutorial - priceless But that's another story. I wonder if we need to talk to the O'Reilly's people about getting a video made or at least allowing an attendee to make one. If someone has a contact there, could they find out? klint. +---+-----+ : Klint Gore: "Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+
Re: [sqlite] Training opportunity: The Inner Workings Of SQLite
On Sun, 19 Jun 2005 15:04:19 -0400, "D. Richard Hipp" <[EMAIL PROTECTED]> wrote: > I will be giving a 3-hour lecture on how SQLite works at > the O'Reilly Open Source Conference in Portland, OR on > 2005-Aug-01. Is there any chance that this will be webcast or recorded (slides/audio/video)? I found presentation notes on O'Reilly's website >from previous events but can't see any from tutorials (and no audio yet). klint. +---+-----+ : Klint Gore: "Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+
Re: [sqlite] NFS Query Performance
On Tue, 19 Apr 2005 12:40:51 -0500, William Hachfeld <[EMAIL PROTECTED]> wrote: > uint64_t t_stop = Now(); > > assert(sqlite3_exec(handle, "COMMIT TRANSACTION;", > NULL, NULL, NULL) == SQLITE_OK); What happens to your insert times if you swap the above 2 lines? If you stop timing before you commit, I would have thought that the journal file writes have been timed instead of the database. If it's a new database then it's probably not going to make any difference, but if it's an existing larger database then it might. klint. +---+-----+ : Klint Gore: "Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+
Re: [sqlite] Finding duplicate records
On Wed, 26 Jan 2005 21:25:19 -0500, "D. Richard Hipp" <[EMAIL PROTECTED]> wrote:
> On Wed, 2005-01-26 at 17:45 -0800, Clark Christensen wrote:
> > I'm new to SQL, and SQLite, and I find myself needing to
> > identify duplicate records in a SQLite table (there are
> > about 2K duplicates in a 36K row table). Any suggestions
> > or magic SQL queries appreciated :-)
> >
>
> SELECT * FROM table EXCEPT SELECT DISTINCT * FROM table;
Does that actually work?
sqlite> create table firstnames (fname varchar(5));
sqlite> insert into firstnames values ('bob');
sqlite> insert into firstnames values ('bob');
sqlite> insert into firstnames values ('bill');
sqlite> select * from firstnames except select distinct * from firstnames;
sqlite>
sqlite> select fname from firstnames group by fname having count(*) > 1;
bob
sqlite>
klint.
+---+-+
: Klint Gore: "Non rhyming:
: EMail : [EMAIL PROTECTED] : slang - the:
: Snail : A.B.R.I.: possibilities :
: Mail University of New England : are useless" :
: Armidale NSW 2351 Australia : L.J.J. :
: Fax : +61 2 6772 5376 : :
+---+-+
