Difficult SQL Statement

From: jim_esti(at)hotmail(dot)com (Jim)
To: pgsql-sql(at)postgresql(dot)org
Subject: Difficult SQL Statement
Date: 2001-05-23 17:20:25
Message-ID: f0e3dc0b.0105230920.441e873e@posting.google.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general 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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Feite Brekeveld 2001-05-23 17:21:07 Re: IpcMemoryCreate
Previous Message Bob Himes 2001-05-23 16:53:52 data/pg_xlog/HUGE_FILE

Browse pgsql-sql by date

  From Date Subject
Next Message Alla 2001-05-23 18:20:14 Return cursor
Previous Message Tom Lane 2001-05-23 16:58:36 Re: index/join madness