I was able to do this, but not with a simple query :-( Here's the solution I came up with. Perhaps someone else knows an easier way to do this? Using your table (called 'RESULTS' in my example): test=# select ASMT_CODE,count(*)::float4 as TOTAL into tmp1 from RESULTS group by ASMT_CODE; Table tmp1 will look like this (if you do a "select * from tmp1"): ASMT_CODE TOTAL -----------------+--------- 1 | 4 2 | 3 3 | 3 4 | 2 test=# select ASMT_CODE,count(*)::float4 as PASSED into tmp2 from RESULTS where STATUS='PASSED' group by ASMT_CODE; Table tmp2 will look like this: ASMT_CODE PASSED ----------------+----------- 1 | 3 2 | 2 3 | 1 4 | 1 test=# select tmp2.ASMT_CODE,tmp2.PASSED/tmp1.TOTAL as PCT into tmp3 from tmp1 natural join tmp2; Table tmp3 will look like this: ASMT_CODE PCT ----------------+------- 1 | 0.75 2 | 0.666667 3 | 0.333333 4 | 0.5 test=# select tmp3.ASMT_CODE,tmp2.PASSED,tmp1.TOTAL,tmp3.PCT from tmp1 natural join tmp2 natural join tmp3; And finally, this select will look like this: ASMT_CODE PASSED TOTAL PCT ----------------+-----------+--------+------------ 1 | 3 | 4 | 0.75 2 | 2 | 3 | 0.666667 3 | 1 | 3 | 0.333333 4 | 1 | 2 | 0.5 To my thinking there's got to be a better way to do this whithout so many temporary tables. Unfortunately my SQL knowledge isn't far enough along to see the way out ;-) Tim ----- Original Message ----- From: "Jim" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, May 23, 2001 10:20 AM Subject: [SQL] Difficult SQL Statement > 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? > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
