Difficult SQL Statement - Mailing list pgsql-sql

From jim_esti@hotmail.com (Jim)
Subject Difficult SQL Statement
Date
Msg-id f0e3dc0b.0105230920.441e873e@posting.google.com
Whole thread Raw
List pgsql-sql
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?


pgsql-sql by date:

Previous
From: alla@sergey.com (Alla)
Date:
Subject: Re: Return cursor
Next
From: Palle Girgensohn
Date:
Subject: Column reference X is ambiguous?