Received: from smtp1.atcominfo.com ([199.106.231.8]) by hub.org (8.10.1/8.10.1) with ESMTP id e8D0kmS40887 for ; Tue, 12 Sep 2000 20:46:48 -0400 (EDT) Received: from BAX8 ([208.60.108.47]) by smtp1.atcominfo.com (Post.Office MTA v3.5.3 release 223 ID# 0-0U10L2S100V35) with SMTP id com for ; Tue, 12 Sep 2000 17:48:12 -0700 Message-Id: <3.0.6.32.20000912204056.008d1990@execulink.com> X-Sender: fbax@execulink.com X-Mailer: QUALCOMM Windows Eudora Light Version 3.0.6 (32) Date: Tue, 12 Sep 2000 20:40:56 -0400 To: pgsql-sql@postgresql.org From: Frank Bax Subject: Re: making two columns out of one In-Reply-To: <3.0.6.32.20000911093447.01fcb4c0@execulink.com> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" X-Archive-Number: 200009/83 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