pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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
Message-ID: <01f101c0e7c6$9fc061a0$a519af3f@hartcomm.com> (raw)
References: <f0e3dc0b.0105230920.441e873e@posting.google.com>

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 &#8216;PASSED&#8217; 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
>




view thread (6+ messages)  latest in thread

Message-ID: <01f101c0e7c6$9fc061a0$a519af3f@hartcomm.com>
Permalink:  ../01f101c0e7c6$9fc061a0$a519af3f@hartcomm.com/
Also on:    postgresql.org/message-id/01f101c0e7c6$9fc061a0$a519af3f@hartcomm.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: tbarnard@povn.com, jim_esti@hotmail.com, pgsql-general@postgresql.org
  Subject: Re: [SQL] Difficult SQL Statement
  In-Reply-To: <01f101c0e7c6$9fc061a0$a519af3f@hartcomm.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox