From: bumby <bumbyn@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Help with query, stuck :(
Date: Thu, 23 Jun 2005 19:03:07 +0200
Message-ID: <3b287bee0506231003213f6c6e@mail.gmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bumbyn@gmail.com
Subject: Re: Help with query, stuck :(
In-Reply-To: <3b287bee0506231003213f6c6e@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox