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?
