[sqlite] Selecting total and not used in one query

2015-12-12 Thread Cecil Westerhof
2015-12-12 21:10 GMT+01:00 Simon Slavin : > > On 12 Dec 2015, at 7:52pm, Cecil Westerhof wrote: > > > ?Here http://www.tutorialspoint.com/sqlite/sqlite_indexes.htm it is said > > that you should not use an index on columns that use a high number of > NULL > > values. At the moment that is true.

[sqlite] Selecting total and not used in one query

2015-12-12 Thread Cecil Westerhof
2015-12-12 19:17 GMT+01:00 Simon Slavin : > > On 12 Dec 2015, at 6:14pm, Cecil Westerhof wrote: > > > ?Not at the moment. But maybe that is a good idea.? > > With clauses like > > >>> WHERE used IS NOT NULL) AS Used > > >>> WHERE used IS NULL > > It should transform the work done. > ?Here

[sqlite] Selecting total and not used in one query

2015-12-12 Thread Simon Slavin
On 12 Dec 2015, at 7:52pm, Cecil Westerhof wrote: > ?Here http://www.tutorialspoint.com/sqlite/sqlite_indexes.htm it is said > that you should not use an index on columns that use a high number of NULL > values. At the moment that is true. So I should not use an Index?? The tutorial thinks

[sqlite] Selecting total and not used in one query

2015-12-12 Thread Cecil Westerhof
2015-12-12 19:00 GMT+01:00 Simon Slavin : > > On 12 Dec 2015, at 2:42pm, Cecil Westerhof wrote: > > >> SELECT > >>(SELECT COUNT(*) FROM proverbs) AS Total > >>, (SELECT COUNT(*) FROM proverbs WHERE used IS NOT NULL) AS Used > >>, (SELECT COUNT(*) FROM proverbs WHERE used IS

[sqlite] Selecting total and not used in one query

2015-12-12 Thread Simon Slavin
On 12 Dec 2015, at 6:14pm, Cecil Westerhof wrote: > ?Not at the moment. But maybe that is a good idea.? With clauses like >>> WHERE used IS NOT NULL) AS Used >>> WHERE used IS NULL It should transform the work done. Simon.

[sqlite] Selecting total and not used in one query

2015-12-12 Thread Simon Slavin
On 12 Dec 2015, at 2:42pm, Cecil Westerhof wrote: >> SELECT >>(SELECT COUNT(*) FROM proverbs) AS Total >>, (SELECT COUNT(*) FROM proverbs WHERE used IS NOT NULL) AS Used >>, (SELECT COUNT(*) FROM proverbs WHERE used IS NULL) AS Free >> > > ?That is what I am using

[sqlite] Selecting total and not used in one query

2015-12-12 Thread Cecil Westerhof
2015-12-12 16:23 GMT+01:00 Igor Tandetnik : > On 12/12/2015 9:18 AM, Cecil Westerhof wrote: > >> But I want something like: >> ?SELECT >> (SELECT COUNT(*) FROM proverbs)AS Total >> , (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used >>

[sqlite] Selecting total and not used in one query

2015-12-12 Thread R Smith
or, more elegantly... WITH PC(t, u, f) AS ( SELECT 1, (P.used IS NOT NULL), (P.used IS NULL) FROM proverbs AS P ) SELECT SUM(PC.t) AS Total, SUM(PC.u) AS Used, SUM(PC.f) AS Free FROM PC; On 2015/12/12 4:18 PM, Cecil Westerhof wrote: > ?I have the following query: > SELECT >

[sqlite] Selecting total and not used in one query

2015-12-12 Thread Cecil Westerhof
2015-12-12 16:06 GMT+01:00 Luuk : > On 12-12-15 15:18, Cecil Westerhof wrote: > >> ?I have the following query: >>> SELECT >>> (SELECT COUNT(*) FROM proverbs) AS Total >>> , (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used >>> >>> But I want something like: >>>

[sqlite] Selecting total and not used in one query

2015-12-12 Thread R Smith
On 2015/12/12 4:18 PM, Cecil Westerhof wrote: > ?I have the following query: > SELECT > (SELECT COUNT(*) FROM proverbs)AS Total > , (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used > > But I want something like: > ?SELECT >

[sqlite] Selecting total and not used in one query

2015-12-12 Thread Luuk
On 12-12-15 15:45, Luuk wrote: > > > On 12-12-15 15:18, Cecil Westerhof wrote: >> ?I have the following query: >> SELECT >> (SELECT COUNT(*) FROM proverbs) AS Total >> , (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used >> >> But I want something like: >> ?

[sqlite] Selecting total and not used in one query

2015-12-12 Thread Luuk
On 12-12-15 15:18, Cecil Westerhof wrote: > ?I have the following query: > SELECT > (SELECT COUNT(*) FROM proverbs)AS Total > , (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used > > But I want something like: > ?SELECT >

[sqlite] Selecting total and not used in one query

2015-12-12 Thread Cecil Westerhof
2015-12-12 15:24 GMT+01:00 R Smith : > > > On 2015/12/12 4:18 PM, Cecil Westerhof wrote: > >> ?I have the following query: >> SELECT >> (SELECT COUNT(*) FROM proverbs)AS Total >> , (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used >> >>

[sqlite] Selecting total and not used in one query

2015-12-12 Thread Cecil Westerhof
?I have the following query: SELECT (SELECT COUNT(*) FROM proverbs)AS Total , (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used But I want something like: ?SELECT (SELECT COUNT(*) FROM proverbs)AS Total

[sqlite] Selecting total and not used in one query

2015-12-12 Thread Igor Tandetnik
On 12/12/2015 9:18 AM, Cecil Westerhof wrote: > But I want something like: > ?SELECT > (SELECT COUNT(*) FROM proverbs)AS Total > , (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used > , (Total - Used)