views:

28

answers:

2

Okay, so I have a (SQLite) table called log wherein one column (time) is a Unix epoch timestamp (the other columns are irrelevant for my purposes). These are timestamped log entries.

I am trying to get a count of how many items are logged per hour (all-time -- I don't care to do it only for a given day or whatever; I'm trying to get an idea of activity density throughout the average day). I have been successful in basically binning these into hours with the query SELECT STRFTIME('%H', DATETIME(time, 'unixepoch', 'localtime')) FROM log;, which then gives me just a huge list of numbers 0-23 which I can run through uniq -c to get a count of events per hour.

However (more out of stubbornness than any practical need, I admit), I want to do this counting step in SQL. My brain got me as far as knowing I'd prrroooobably have to do some kind of self-join to get only the unique values for the generated hours column, but that's where it crapped out D: Any ideas?

+2  A: 
SELECT STRFTIME('%H', DATETIME(time, 'unixepoch', 'localtime')), 
COUNT(anotherField)
FROM log
GROUP BY STRFTIME('%H', DATETIME(time, 'unixepoch', 'localtime'));

Note: I haven't worked with SQLite. However, it supports GROUP BY and hence, this should work.

shahkalpesh
+1: GROUP BY is required in standard SQL, but [SQLite (like MySQL) does support hidden columns](http://www.sqlite.org/lang_select.html)
OMG Ponies
Oh man, I forgot all about GROUP BY, shows how rusty I am. This query might be just a little slow but for what I'm dealing with it's really just fine. By the way, it's probably better to SELECT STRFTIME(...) AS hour [...] GROUP BY hour.
tgies
@tgies: I don't know if SQLite supports `GROUP BY hour` kind of syntax because I haven't worked with it. Other DBs (such as SQL Server) don't support column alias in group by, IIRC.
shahkalpesh
@shahkalpesh: Worked fine here.
tgies
SQLite does support grouping by alias. It's the best way not to make a huge accident. Come to think of it, Oracle and MySQL do too, so perhaps it is SQL Server which stands out by not supporting it?
MPelletier
+1  A: 

why use COUNT(anotherField)?

Use SELECT [your expression], COUNT(*) FROM log GROUP BY [your expression]

Benoit