I'm having a bit of a brain freeze and can't seem to come up with
decent SQL for the following problem:
I have a table "t" of the form "time_occurred TIMESTAMP, prog_data
VARCHAR" and would like to create a query that outputs something of
the form "first_seen, last_seen, count, prog_data".
I have the current query which gets the first_seen and last_seen via
subqueries, ala
SELECT t1.time_occurred AS first_seen, t2.time_occurred AS last_seen,
t3.count, t1.prog_data
FROM t AS t1, t AS t2
WHERE t1.prog_data = t2.prog_data AND t1.time_occurred IN (SELECT min(time_occurred) FROM t WHERE
prog_data = t1.prog_data) AND t2.time_occurred IN (SELECT max(time_occurred) FROM t WHERE
prog_data = t1.prog_data)
but I can't seem to work out how to get the count of all the records
that have. I figure that this is probably a relatively common idiom
... can anyone suggest ways to go about doing this. Also, the
performance of this is pretty horrible, but I figure that creating a
column on t.prog_data should speed things up noticably, right?
Thanks!