Displaying first, last, count columns - Mailing list pgsql-sql

From Worky Workerson
Subject Displaying first, last, count columns
Date
Msg-id ce4072df0606210755j40941cf7v44648f2a0e69be56@mail.gmail.com
Whole thread Raw
Responses Re: Displaying first, last, count columns
List pgsql-sql
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!


pgsql-sql by date:

Previous
From: Hélder M. Vieira
Date:
Subject: Re: [SQL] Problema com função UPPER
Next
From: "Aaron Bono"
Date:
Subject: Re: Displaying first, last, count columns