Creating a histogram in SQL using event data

Sometimes you may have gathered some data on event-by-event basis. For instance, the data may look like this when pulses from a radioactive decay have been processed by a peak sensing analog to digital converter (ADC):

2
2
2
8
8
8
8
8
8
8
8
8
100
100
250
250

A simple table in a relational database may be created to store this data:

CREATE TABLE samples( channel INTEGER NOT NULL );

BEGIN TRANSACTION;
INSERT INTO samples VALUES(2);
INSERT INTO samples VALUES(2);
INSERT INTO samples VALUES(2);
INSERT INTO samples VALUES(8);
INSERT INTO samples VALUES(8);
INSERT INTO samples VALUES(8);
INSERT INTO samples VALUES(8);
INSERT INTO samples VALUES(8);
INSERT INTO samples VALUES(8);
INSERT INTO samples VALUES(8);
INSERT INTO samples VALUES(8);
INSERT INTO samples VALUES(8);
INSERT INTO samples VALUES(100);
INSERT INTO samples VALUES(100);
INSERT INTO samples VALUES(250);
INSERT INTO samples VALUES(250);
COMMIT;

I found the following SQL statement somewhere on the net (if you find it, please let me know where so that I can pay the appropriate tribute to its source). It is a demonstration of how to create a 1024 channel histogram with the above data. I hope you find it useful.

SELECT bin, COUNT(1) AS cnt
FROM (SELECT CAST((channel-mn) / (1.0*range/1024) AS INTEGER) + 1 AS bin
      FROM (SELECT MIN(channel) AS mn, MAX(channel)-MIN(channel)+1 AS range
            FROM samples) AS R
         CROSS JOIN
           (SELECT * FROM samples) AS S) AS RS
GROUP BY bin;

Using SQLite as database engine, I get the following output after executing the above SQL statements.

1|3
25|9
404|2
1020|2