Difficult SQL Statement
От | jim_esti@hotmail.com (Jim) |
---|---|
Тема | Difficult SQL Statement |
Дата | |
Msg-id | f0e3dc0b.0105230920.441e873e@posting.google.com обсуждение исходный текст |
Список | 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 по дате отправления: