Re: [SQL] Difficult SQL Statement - Mailing list pgsql-general

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" <jim_esti@hotmail.com>
To: <pgsql-sql@postgresql.org>
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 majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>