On Wed, Feb 19, 2014 at 9:04 AM, Patrick Proniewski <pat...@patpro.net>wrote:
> 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?
>
Maybe this:
SELECT strftime('%Y', CreateDate, 'unixepoch') AS year,
sum(iso==100)
FROM exifdata WHERE year NOT NULL
GROUP BY year
ORDER BY year;
The main trick is to use "sum(iso==100)" instead of "count(iso)". Note
also that SQL strings use a single quote, not a double-quote. And the
datetime() function call is redundant as strftime can do the unixepoch
conversion for you. And you don't need to GROUP BY iso, since you are only
interested in a single iso value.
>
> 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
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
