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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
