Hello,
I'm currently designing a small tool with shell scripts, sqlite3 backend, and
gnuplot for graphical output.
My SQLite database has one table, each row being a picture name and a subset of
its EXIF tags (date, ISO, metering mode...).
In order to process data into gnuplot, I use SELECT to output what I need in a
flat file.
Example:
For ISO = 100, I issue this request:
SELECT strftime("%Y", datetime(CreateDate,"unixepoch")) as year, COUNT(ISO)
FROM exifdata WHERE ISO = 100 AND year not null GROUP BY ISO, year ORDER BY
year;
It returns :
2008 9
2009 581
2010 3144
2011 768
2012 1195
2013 1270
That is correct, but not enough: the database includes pictures taken in 2014,
none of which at ISO = 100, but I need a line "2014 0" in my output (otherwise
gnuplot mixes things when it draws stacked histograms).
My question is: how can I write a request that will successfully pull a result
for every years, even if the count is 0?
I've created a second table "years", with only one column ("year"), rows are
"2008", "2009"..., "2014". Then I've tried using JOIN to get at least a NULL
result, without success.
Full table with create statement is available here:
http://patpro.net/~patpro/exifdata.sql.zip (106 kB).
Any hint appreciated!
Thanks,
Patrick
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
