Skip site navigation (1) Skip section navigation (2)

operating on data from multiple rows?

From: Joshua Daniel Franklin <joshua(at)iocc(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: operating on data from multiple rows?
Date: 2002-10-22 19:06:43
Message-ID: Pine.LNX.4.44.0210221351200.29304-100000@iocc.com (view raw or flat)
Thread:
Lists: pgsql-novice
Here is a problem I've run into with an old IMHO poorly designed database:

There is a table ("log") that has fields 

username, sessionid, loggedtime, loggeddate, accntstatus

A SELECT might return this data, for example: 

bob	1035208	2002-10-11	11:32:00	Start
bob	1035208	2002-10-11	11:38:00	Stop
bob	1052072	2002-10-12	10:05:00	Start
bob	1052072	2002-10-12	10:15:00	Stop

I'm trying to get my head around a SELECT that will return
only one entry per sessionid with a duration instead of two entries for
each. If I had two separate tables for Start and Stop it would
be trivial with a join, but all I can think of is doing a 
"SELECT ... WHERE accntstatus = 'Start'" and then grabbing the 
sessionid and doing a separate SELECT for every record (and then the
math to get the duration). This seems like a bad idea since thousands 
of records are retrived at a time.
Am I missing a better way?

-- 
Joshua Daniel Franklin
Network Administrator
IOCC.COM



Responses

pgsql-novice by date

Next:From: Michael PaesoldDate: 2002-10-22 19:52:52
Subject: Re: operating on data from multiple rows?
Previous:From: Tom AnsleyDate: 2002-10-22 18:01:20
Subject: test

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group