Received: from hcs.hartcomm.com ([63.175.25.2]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f4TGiJE88394 for ; Tue, 29 May 2001 12:44:19 -0400 (EDT) (envelope-from tbarnard@povn.com) Received: from dev6 (dev5.hartcomm.com [63.175.25.165]) by hcs.hartcomm.com (8.11.0/8.8.7) with SMTP id f4TGmvZ23382; Tue, 29 May 2001 09:48:57 -0700 Message-ID: <002f01c0e85e$a56b0cc0$a519af3f@hartcomm.com> From: "Tim Barnard" To: "Tom Lane" Cc: References: <01f101c0e7c6$9fc061a0$a519af3f@hartcomm.com> <18343.991143963@sss.pgh.pa.us> <18584.991147846@sss.pgh.pa.us> Subject: Re: Re: [SQL] Difficult SQL Statement Date: Tue, 29 May 2001 09:44:35 -0700 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Archive-Number: 200105/1137 X-Sequence-Number: 10187 Thanks for the helpful tip! Tim ----- Original Message ----- From: "Tom Lane" To: "Tim Barnard" Cc: "Jim" ; Sent: Tuesday, May 29, 2001 7:50 AM Subject: Re: [GENERAL] Re: [SQL] Difficult SQL Statement > I wrote: > > select ASMT_CODE, PASSED, TOTAL, PASSED::float4/TOTAL::float4 > > from > > (select ASMT_CODE, count(*) as TOTAL from RESULTS > > group by ASMT_CODE) as tmp1 > > natural join > > (select ASMT_CODE, count(*) as PASSED from RESULTS > > where STATUS='PASSED' group by ASMT_CODE) as tmp2 > > BTW, although this is a fine example of how to avoid using temp tables, > it's not such a great solution to the original problem. What happens > if there are no 'PASSED' entries at all for a given ASMT_CODE? You > probably won't want that ASMT_CODE to disappear from your report --- > but it will disappear in the join. We could fix this with a left join > and some hackery to deal with the resulting NULL values for PASSED, > but now things are getting ugly. Time to try something different: > > select ASMT_CODE, PASSED, TOTAL, PASSED::float4/TOTAL::float4 as PCT > from > (select ASMT_CODE, > count(*) as TOTAL, > sum(CASE WHEN STATUS='PASSED' THEN 1 ELSE 0 END) as PASSED > from RESULTS group by ASMT_CODE) as tmp1 > > Here we use the sub-select only as a way of labeling the count() and > sum() results so that we don't have to write and compute them twice. > You could write it as a simple one-level SELECT if you didn't mind > that redundancy. > > regards, tom lane >