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