Thread: Re: [SQL] Difficult SQL Statement
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 >
"Tim Barnard" <tbarnard@povn.com> writes:
> To my thinking there's got to be a better way to do this whithout so many
> temporary tables.
In 7.1 you can frequently replace temp tables with subselect-in-FROM.
Cutting-and-pasting freely from your solution:
select ASMT_CODE, PASSED, TOTAL, PASSED::float4/TOTAL::float4
from
(select ASMT_CODE, count(*) as TOTAL from RESULTS
group by ASMT_CODE) as tmp1
natural join
(select ASMT_CODE, count(*) as PASSED from RESULTS
where STATUS='PASSED' group by ASMT_CODE) as tmp2
(haven't tried this, but it looks right...)
This won't necessarily be a whole lot faster than the solution with
temp tables, but it's nice not to have to worry about dropping the
temp tables afterwards.
regards, tom lane
I wrote:
> select ASMT_CODE, PASSED, TOTAL, PASSED::float4/TOTAL::float4
> from
> (select ASMT_CODE, count(*) as TOTAL from RESULTS
> group by ASMT_CODE) as tmp1
> natural join
> (select ASMT_CODE, count(*) as PASSED from RESULTS
> where STATUS='PASSED' group by ASMT_CODE) as tmp2
BTW, although this is a fine example of how to avoid using temp tables,
it's not such a great solution to the original problem. What happens
if there are no 'PASSED' entries at all for a given ASMT_CODE? You
probably won't want that ASMT_CODE to disappear from your report ---
but it will disappear in the join. We could fix this with a left join
and some hackery to deal with the resulting NULL values for PASSED,
but now things are getting ugly. Time to try something different:
select ASMT_CODE, PASSED, TOTAL, PASSED::float4/TOTAL::float4 as PCT
from
(select ASMT_CODE,
count(*) as TOTAL,
sum(CASE WHEN STATUS='PASSED' THEN 1 ELSE 0 END) as PASSED
from RESULTS group by ASMT_CODE) as tmp1
Here we use the sub-select only as a way of labeling the count() and
sum() results so that we don't have to write and compute them twice.
You could write it as a simple one-level SELECT if you didn't mind
that redundancy.
regards, tom lane
Thanks for the helpful tip! Tim ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Tim Barnard" <tbarnard@povn.com> Cc: "Jim" <jim_esti@hotmail.com>; <pgsql-general@postgresql.org> Sent: Tuesday, May 29, 2001 7:50 AM Subject: Re: [GENERAL] Re: [SQL] Difficult SQL Statement > I wrote: > > select ASMT_CODE, PASSED, TOTAL, PASSED::float4/TOTAL::float4 > > from > > (select ASMT_CODE, count(*) as TOTAL from RESULTS > > group by ASMT_CODE) as tmp1 > > natural join > > (select ASMT_CODE, count(*) as PASSED from RESULTS > > where STATUS='PASSED' group by ASMT_CODE) as tmp2 > > BTW, although this is a fine example of how to avoid using temp tables, > it's not such a great solution to the original problem. What happens > if there are no 'PASSED' entries at all for a given ASMT_CODE? You > probably won't want that ASMT_CODE to disappear from your report --- > but it will disappear in the join. We could fix this with a left join > and some hackery to deal with the resulting NULL values for PASSED, > but now things are getting ugly. Time to try something different: > > select ASMT_CODE, PASSED, TOTAL, PASSED::float4/TOTAL::float4 as PCT > from > (select ASMT_CODE, > count(*) as TOTAL, > sum(CASE WHEN STATUS='PASSED' THEN 1 ELSE 0 END) as PASSED > from RESULTS group by ASMT_CODE) as tmp1 > > Here we use the sub-select only as a way of labeling the count() and > sum() results so that we don't have to write and compute them twice. > You could write it as a simple one-level SELECT if you didn't mind > that redundancy. > > regards, tom lane >