Re: [SQL] Difficult SQL Statement

Great!
If you're asking if the last column can be changed to THE_COUNT/TOTAL_NO*100
it can. Just change the -- as TOTAL_PERCENTAGE -- to
as "THE_COUNT/TOTAL_NO*100"

Tim

----- Original Message -----
From: "Jim Russell" <jim_esti(at)hotmail(dot)com>
To: <tbarnard(at)povn(dot)com>
Sent: Wednesday, May 30, 2001 12:16 PM
Subject: Re: [SQL] Difficult SQL Statement

> Thanks for the ideas. Here is what I have come up with so far. I have
> eliminated the need to temporary tables. The SQL statement works great.
>
> select R1.ASMT_CODE, sum(case when R1.status = 'PASSED' then 1 else 0 end)
> as PASSED, count(*) as TOTAL_NO, sum(case when R1.status = 'PASSED' then 1
> else 0 end)/count(*)*100 as TOTAL_PRECENTAGE
> from RESULT R1
> group by R1.ASMT_CODE
> order by R1.ASMT_CODE
>
> Results in:
>
> ASMT_CODE THE_COUNT TOTAL_NO TOTAL_PRECENTAGE
> 1 3 4 75
> 2 2 3 66.67
> 3 1 3 33.34
> 4 1 2 50
>
>
> Back Ground stuff:
>
> It would look perfect if the results looked like:
> ASMT_CODE THE_COUNT TOTAL_NO THE_COUNT/TOTAL_NO*100
> 1 3 4 75
> 2 2 3 66.67
> 3 1 3 33.34
> 4 1 2 50
>
> Source TAB (AKA: RESULTS table)
> 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
>
>
>
> >From: "Tim Barnard" <tbarnard(at)povn(dot)com>
> >To: "Jim" <jim_esti(at)hotmail(dot)com>
> >CC: <pgsql-general(at)postgresql(dot)org>
> >Subject: Re: [SQL] Difficult SQL Statement
> >Date: Mon, 28 May 2001 15:36:22 -0700
> >
> >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(at)hotmail(dot)com>
> >To: <pgsql-sql(at)postgresql(dot)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(at)postgresql(dot)org so that your
> > > message can get through to the mailing list cleanly
> > >
> >
>
> _________________________________________________________________________
> Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
>
>