Received: from C9Mailgw02.prontomail.com ([216.163.188.10]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f4UDFkE67294 for ; Wed, 30 May 2001 09:15:47 -0400 (EDT) (envelope-from rdg@viafractal.com.br) Received: by C9Mailgw02.prontomail.com (NPlex 5.5.029) id 3B08177900183D1F for pgsql-sql@postgresql.org; Wed, 30 May 2001 06:12:35 -0700 Received: from 200.176.160.245 by SmtpServer for ; Wed, 30 May 2001 13:12:34 +0000 Message-ID: <3B14F4C4.EAEA74C6@viafractal.com.br> Date: Wed, 30 May 2001 10:25:24 -0300 From: Renato De Giovanni X-Mailer: Mozilla 4.72 [en] (Win95; I) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: Difficult SQL Statement References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200105/326 X-Sequence-Number: 3167 > 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? You can get the first 3 columns with one statement - the fourth column should be calculated outside the query. Try this: select d1.asmt_code, count(case when d1.status = 'PASSED' then 1 else NULL end) as passed, count(d1.amst_code) as total from test_run d1 group by d1.asmt_code order by d1.asmt_code HTH, -- Renato Sao Paulo - SP - Brasil rdg@viafractal.com.br