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@viafractal.com.br