DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=Na9hZfMY+7wJdADIp4eCmV4tZ79AQxTTMBAb/61Nrsm6KEKLPEBnSUvrxN9ySGiv/StBd7SV5cc3aj31KK70f6uoPn7T1BpFfHxvPEE5WoJovc7MnOE4FZvINGQA+1YJDRIWcrR2sBNj1/5boAFPzqaEpOjdimYNgXPOk9o7MdY=
Message-ID: <ce4072df0606210755j40941cf7v44648f2a0e69be56@mail.gmail.com>
Date: Wed, 21 Jun 2006 10:55:54 -0400
From: "Worky Workerson" <worky.workerson@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Displaying first, last, count columns
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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!