@ Jean-Christophe Deschamps and Roger Andersson Thanks A LOT :-) That is exactly what I was looking for. I do appreciate your help.
Txs again bernie ---------------------------------------------------------------------- 13. SELECT question (computing day of week the using strftime() function) (Bernie Reiter) 14. Re: SELECT question (computing day of week the using strftime() function) (Jean-Christophe Deschamps) 16. Re: SELECT question (computing day of week the usingstrftime() function) (Roger Andersson) ---------------------------------------------------------------------- Message: 13 Date: Sat, 1 May 2010 21:54:37 +0000 (GMT) From: Bernie Reiter <bernie_on_the_road_ag...@yahoo.co.uk> Subject: [sqlite] SELECT question (computing day of week the using strftime() function) To: sqlite-users@sqlite.org Message-ID: <606363.46898...@web25905.mail.ukl.yahoo.com> Content-Type: text/plain; charset=utf-8 Dear List Members, I do need your wisdom and experience. Thank you very much for sharing these with me ! I have a table into which I import data records from an external source. These imported data records are all containing a date field. I can change the field definitions in this table without any problems. I need to find out how many specific weekdays (e.g., how many Sundays) I have in any given range of dates. My problem: How to use the COUNT function in combination with the strftime() function. $ sqlite3 test.db3 SQLite version 3.6.20 sqlite> create table test (date VARCHAR(20), money INTEGER); sqlite> INSERT INTO "test" VALUES('2007-07-20', 1000); sqlite> INSERT INTO "test" VALUES('2007-07-21', 2100); sqlite> INSERT INTO "test" VALUES('2007-07-22', 2200); sqlite> SELECT date from test ...> WHERE date BETWEEN '2007-07-20' AND '2007-07-22'; 2007-07-20 2007-07-21 2007-07-22 sqlite> SELECT COUNT(date) FROM test ...> WHERE date BETWEEN '2007-07-20' AND '2007-07-22'; 3 sqlite> SELECT COUNT(date) FROM test ...> WHERE date = '2007-07-22'; 1 sqlite> SELECT strftime('%w', date) FROM test ...> WHERE date BETWEEN '2007-07-20' AND '2007-07-22'; 5 6 0 => BERNIE'S COMMENT: 2007-07-22 is a Sunday <= sqlite> SELECT strftime('%w', date) FROM test ...> WHERE date = '2007-07-22'; 0 Perfect: I have 3 data records, only one of which is a Sunday. I can see this in this easy example immediately, but does SQLite too? I can import the result list of "SELECT strftime('%w', date) FROM test ..." into awk to do the counting there, but maybe there is a nice SQL way... I have tried to construct a SELECT statement to count how many Sundays I have in my data records. I have tried many variantions of (sub)selects but I didn't get anywhere. I don't want to "overflow" my posting by listing them all. I will show only 3 as examples which I used to check my SELECT statements. Here in these 3 examples I have not yet used the COUNT function... sqlite> SELECT date AS XXX FROM test ...> WHERE date BETWEEN '2007-07-20' AND '2007-07-22' ...> AND (strftime('%w', XXX)) = 0; sqlite> SELECT date AS XXX FROM test ...> WHERE XXX BETWEEN '2007-07-20' AND '2007-07-22' ...> AND (strftime('%w', date)) = 0; sqlite> SELECT date AS XXX FROM test ...> WHERE XXX BETWEEN '2007-07-20' AND '2007-07-22' ...> AND date = (SELECT date FROM test WHERE (strftime('%w', date)) = 0); Unfortunately, in all 3 examples the result of my SELECT statements is empty. I have then enclosed the 0 for Sunday in single quotes: sqlite> SELECT date AS XXX FROM test ...> WHERE XXX BETWEEN '2007-07-20' AND '2007-07-22' ...> AND date = (SELECT date FROM test WHERE (strftime('%w', date)) = '0'); 2007-07-22 But my whole SELECT looks clumsy. Any suggestions? And I have no clue why I should enclose the integer result of a function (i.e., here 0) in quotes... Thanks again for your help and do enjoy your weekend :-) bernie ------------------------------ Message: 14 Date: Sun, 02 May 2010 01:50:08 +0200 From: Jean-Christophe Deschamps <j...@q-e-d.org> Subject: Re: [sqlite] SELECT question (computing day of week the using strftime() function) To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Message-ID: <7.0.1.0.2.20100502014723.02ac0...@q-e-d.org> Content-Type: text/plain; charset="us-ascii"; format=flowed >I need to find out how many specific weekdays (e.g., how many Sundays) >I have in any given range of dates. >My problem: How to use the COUNT function in combination with the >strftime() function. > >$ sqlite3 test.db3 >SQLite version 3.6.20 >sqlite> create table test (date VARCHAR(20), money INTEGER); >sqlite> INSERT INTO "test" VALUES('2007-07-20', 1000); >sqlite> INSERT INTO "test" VALUES('2007-07-21', 2100); >sqlite> INSERT INTO "test" VALUES('2007-07-22', 2200); That will do: select count(*) from test where date between '2007-07-20' and '2007-07-22' and strftime('%w', date) = '0'; This will select and count, not compute anything. ------------------------------ Message: 16 Date: Sun, 2 May 2010 07:24:38 +0200 From: "Roger Andersson" <r...@telia.com> Subject: Re: [sqlite] SELECT question (computing day of week the usingstrftime() function) To: "'General Discussion of SQLite Database'" <sqlite-users@sqlite.org> Message-ID: <94ae5b7b75dd46b4820308ee032d8...@prescot> Content-Type: text/plain; charset="us-ascii" > I need to find out how many specific weekdays (e.g., how many > Sundays) I have in any given range of dates. > My problem: How to use the COUNT function in combination with > the strftime() function. > Maybe something like sqlite3 test.db SQLite version 3.6.23 sqlite> CREATE TABLE test (date TEXT, money INTEGER); sqlite> INSERT INTO test VALUES('2007-07-20', 1000); sqlite> INSERT INTO test VALUES('2007-07-21', 2100); sqlite> INSERT INTO test VALUES('2007-07-22', 2200); sqlite> INSERT INTO test VALUES('2007-07-27', 7000); sqlite> INSERT INTO test VALUES('2007-07-28', 2800); sqlite> INSERT INTO test VALUES('2007-07-29', 2900); sqlite> INSERT INTO test VALUES('2007-07-22', 9200); sqlite> SELECT strftime('%w', date) weekday, count(*) cnt FROM test GROUP BY weekday; weekday|cnt 0|3 5|2 6|2 ------------------------------ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users End of sqlite-users Digest, Vol 29, Issue 2 ******************************************* _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users