Received: from mail1.hub.org (webmail.hub.org [216.126.85.1]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f4T3WhE26163 for ; Mon, 28 May 2001 23:32:43 -0400 (EDT) (envelope-from tbarnard@povn.com) Received: from hcs.hartcomm.com ([63.175.25.2]) by mail1.hub.org (8.11.3/8.11.1) with ESMTP id f4T33A202829 for ; Mon, 28 May 2001 23:03:11 -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 f4SMeZZ21095; Mon, 28 May 2001 15:40:36 -0700 Message-ID: <01f101c0e7c6$9fc061a0$a519af3f@hartcomm.com> From: "Tim Barnard" To: "Jim" Cc: References: Subject: Re: [SQL] Difficult SQL Statement Date: Mon, 28 May 2001 15:36:22 -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/1119 X-Sequence-Number: 10168 I was able to do this, but not with a simple query :-( Here's the solution I came up with. Perhaps someone else knows an easier way to do this? Using your table (called 'RESULTS' in my example): test=# select ASMT_CODE,count(*)::float4 as TOTAL into tmp1 from RESULTS group by ASMT_CODE; Table tmp1 will look like this (if you do a "select * from tmp1"): ASMT_CODE TOTAL -----------------+--------- 1 | 4 2 | 3 3 | 3 4 | 2 test=# select ASMT_CODE,count(*)::float4 as PASSED into tmp2 from RESULTS where STATUS='PASSED' group by ASMT_CODE; Table tmp2 will look like this: ASMT_CODE PASSED ----------------+----------- 1 | 3 2 | 2 3 | 1 4 | 1 test=# select tmp2.ASMT_CODE,tmp2.PASSED/tmp1.TOTAL as PCT into tmp3 from tmp1 natural join tmp2; Table tmp3 will look like this: ASMT_CODE PCT ----------------+------- 1 | 0.75 2 | 0.666667 3 | 0.333333 4 | 0.5 test=# select tmp3.ASMT_CODE,tmp2.PASSED,tmp1.TOTAL,tmp3.PCT from tmp1 natural join tmp2 natural join tmp3; And finally, this select will look like this: ASMT_CODE PASSED TOTAL PCT ----------------+-----------+--------+------------ 1 | 3 | 4 | 0.75 2 | 2 | 3 | 0.666667 3 | 1 | 3 | 0.333333 4 | 1 | 2 | 0.5 To my thinking there's got to be a better way to do this whithout so many temporary tables. Unfortunately my SQL knowledge isn't far enough along to see the way out ;-) Tim ----- Original Message ----- From: "Jim" To: Sent: Wednesday, May 23, 2001 10:20 AM Subject: [SQL] Difficult SQL Statement > I have a table that has the following columns: AUTHOR_NO, ASMT_CODE, & > STATUS. The table would look something like this: > AUTHOR_NO ASMT_CODE STATUS > 12345 1 PASSED > 12345 2 FAILED > 12345 3 FAILED > 12345 4 PASSED > 12346 1 PASSED > 12346 2 PASSED > 12346 3 PASSED > 654321 1 FAILED > 654321 2 PASSED > 654321 3 FAILED > 654321 4 FAILED > 000123 1 PASSED > > So I am trying to write a SQL statement that will return the > ASMT_CODE, the total number of ‘PASSED’ for the ASMT_CODE, > the total number of participants for that ASMT_CODE and finally a > percent of the PASSED for that particular ASMT_CODE over the number of > participants for that ASMT_CODE. > So, if I had the table above I would get something like this: > > ASMT_CODE # PASSED TOTAL # % of Total > 1 3 4 75 > 2 2 3 66.67 > 3 1 3 33.34 > 4 1 2 50 > > As you notice I am look for the ASMT_CODE base percentage rather than > the over all percentage. What would be the SQL to do this? > > I have tried to write this, but cannot figure out how to calculate the > last two columns. Here is what I have so far: > select d1.asmt_code, count(d1.amst_code) > from test_run d1 > where d1.status = 'PASSED' > group by d1.asmt_code > order by d1.asmt_code > BUT this only yields me the first two columns. > > CAN ANYONE HELP? > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >