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

Re: operating on data from multiple rows?

From: "Michael Paesold" <mpaesold(at)gmx(dot)at>
To: "Joshua Daniel Franklin" <joshua(at)iocc(dot)com>,<pgsql-novice(at)postgresql(dot)org>
Subject: Re: operating on data from multiple rows?
Date: 2002-10-22 19:52:52
Message-ID: 01f701c27a04$9c5db540$4201a8c0@beeblebrox (view raw or flat)
Thread:
Lists: pgsql-novice
Joshua Daniel Franklin <joshua(at)iocc(dot)com> wrote:

> 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?

A self-join would help...

SELECT start.username, start.sessionid,
    ((stop.loggeddate + stop.loggedtime)
        - (start.loggeddate + start.loggedtime)) as duration
  FROM log AS start, log AS stop
  WHERE start.accntstatus = 'Start'
    AND stop.accntstatus = 'Stop'
    AND start.sessionid = stop.sessionid;

(not tested, but try like this)
You probably have to cast the value of the duration.

Best Regards,
Michael Paesold


In response to

Responses

pgsql-novice by date

Next:From: Andrew McMillanDate: 2002-10-22 20:19:01
Subject: Re: operating on data from multiple rows?
Previous:From: Joshua Daniel FranklinDate: 2002-10-22 19:06:43
Subject: operating on data from multiple rows?

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