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

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@hotmail.com>
To: <tbarnard@povn.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@povn.com>
> >To: "Jim" <jim_esti@hotmail.com>
> >CC: <pgsql-general@postgresql.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@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
> > >
> >
>
> _________________________________________________________________________
> Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
>
>