Received: from postgresql.org.org (webmail.postgresql.org [216.126.85.28]) by postgresql.org (8.11.3/8.11.1) with SMTP id f4SCTZA92774 for ; Mon, 28 May 2001 08:29:35 -0400 (EDT) (envelope-from pgsql-sql-owner@postgresql.org) Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f4NHRiA77892 for ; Wed, 23 May 2001 13:27:44 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.1/8.11.1) id f4NHKh315725 for pgsql-sql@postgresql.org; Wed, 23 May 2001 13:20:43 -0400 (EDT) (envelope-from news) From: jim_esti@hotmail.com (Jim) X-Newsgroups: comp.databases.postgresql.sql Subject: Difficult SQL Statement Date: 23 May 2001 10:20:25 -0700 Organization: http://groups.google.com/ Lines: 42 Message-ID: Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Complaints-To: groups-support@google.com To: pgsql-sql@postgresql.org X-Archive-Number: 200105/295 X-Sequence-Number: 3126 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?