pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedmaking two columns out of one
2+ messages / 1 participants
[nested] [flat]
* making two columns out of one
@ 2000-09-11 13:34 Frank Bax <fbax@execulink.com>
2000-09-13 00:40 ` Re: making two columns out of one Frank Bax <fbax@execulink.com>
0 siblings, 1 reply; 2+ messages in thread
From: Frank Bax @ 2000-09-11 13:34 UTC (permalink / raw)
To: pgsql-sql
I've got a table containing some timesheet data.
Fields are emp, earncode, lo_shift, hi_shift.
SELECT emp, sum(hi_shift - lo_shift) as reghrs from timesheet
where earncode in ('R', 'C', 'X') order by emp
will give me all the regular hours
SELECT emp, sum(hi_shift - lo_shift) as ovrhrs from timesheet
where earncode not in ('R', 'C', 'X') order by emp
will give me all the overtime hours.
How do I combine these to get one result set with emp, reghrs, ovrhrs on
each row.
Frank
________________________________________________________
1stUp.com - Free the Web
Get your free Internet access at http://www.1stUp.com
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: making two columns out of one
2000-09-11 13:34 making two columns out of one Frank Bax <fbax@execulink.com>
@ 2000-09-13 00:40 ` Frank Bax <fbax@execulink.com>
0 siblings, 0 replies; 2+ messages in thread
From: Frank Bax @ 2000-09-13 00:40 UTC (permalink / raw)
To: pgsql-sql
It took quite a bit of trial and error, but I finally got it!
SELECT emp,
SUM (CASE WHEN earncode IN ('R','C','X') THEN (hi_shift-lo_shift)
ELSE 0 END) AS reghrs,
SUM (CASE WHEN earncode IN ('R','C','X') THEN 0 ELSE
(hi_shift-lo_shift) END) AS ovrhrs
FROM timesheet
GROUP BY emp
ORDER BY emp;
At 09:34 AM 9/11/00 -0400, Frank Bax wrote:
>I've got a table containing some timesheet data.
>Fields are emp, earncode, lo_shift, hi_shift.
>
>SELECT emp, sum(hi_shift - lo_shift) as reghrs from timesheet
>where earncode in ('R', 'C', 'X') order by emp
>
>will give me all the regular hours
>
>SELECT emp, sum(hi_shift - lo_shift) as ovrhrs from timesheet
>where earncode not in ('R', 'C', 'X') order by emp
>
>will give me all the overtime hours.
>
>How do I combine these to get one result set with emp, reghrs, ovrhrs on
>each row.
>
>Frank
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2000-09-13 00:40 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-09-11 13:34 making two columns out of one Frank Bax <fbax@execulink.com>
2000-09-13 00:40 ` Frank Bax <fbax@execulink.com>
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