Re: 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?

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(at)viafractal(dot)com(dot)br