Help with query, stuck :(

From: bumby <bumbyn(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Help with query, stuck :(
Date: 2005-06-23 17:03:07
Message-ID: 3b287bee0506231003213f6c6e@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

I have a table as

Table "public.timereport"
Column | Type |
Modifiers
----------+-----------------------------+------------------------------------------------------------
id | integer | not null default
nextval('public.timereport_id_seq'::text)
employee | character varying(10) |
start | timestamp without time zone | default
('now'::text)::timestamp without time zone
finish | timestamp without time zone |
break | interval |
flags | character(8) |
authed | boolean | default false

I want to do a SUM, AVG, MAX and MIN on finish-start-break to get the
total time worked for the periode stated in the WHERE expression.
(like where employee='Martin' and extract(week from start)=5). I also
want to calculate an overtime like

SUM(finish-start-break::interval)-COUNT(*)*'8 hours'::interval

Problem is, as I noticed, that if one signs in and out more then one
time per day, the overtime calculation will not work (since I subtract
8 hours for each sign-in/out.

So I guess I'll have to do a distinct select on date_trunc('day',
start). Best I can come up with is

SELECT
SUM(x.finish-x.start-x.break::interval) as stat_tot,
AVG(x.finish-x.start-x.break::interval) as stat_avg,
MAX(x.finish-x.start-x.break::interval) as stat_max,
MIN(x.finish-x.start-x.break::interval) as stat_min,
SUM(x.finish-x.start-x.break::interval)-COUNT(*)*'8 hours'::interval
as stat_otime
FROM (
SELECT DISTINCT ON (date_trunc('day', start)) * FROM
timereport WHERE employee='Martin'
) AS x
GROUP BY id,start,employee ORDER BY date_trunc('day', start), id, employee;

This however returns one SUM,AVG, etc row for each row in the
sub-query instead of doing what I want - having the sub-query return
all rows and letting SUM etc function do their work on those rows.

Any suggestions?

Thanks in advance,

bumby

Browse pgsql-sql by date

  From Date Subject
Next Message Markus Bertheau 2005-06-23 17:31:57 optimizer, view, union
Previous Message Russell Simpkins 2005-06-23 13:15:41 Re: after delete trigger behavior