pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: jim_esti@hotmail.com
To: pgsql-sql@postgresql.org
Subject: Difficult SQL Statement
Date: 23 May 2001 10:20:25 -0700
Message-ID: <f0e3dc0b.0105230920.441e873e@posting.google.com> (raw)

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?



view thread (6+ messages)  latest in thread

Message-ID: <f0e3dc0b.0105230920.441e873e@posting.google.com>
Permalink:  ../f0e3dc0b.0105230920.441e873e@posting.google.com/
Also on:    postgresql.org/message-id/f0e3dc0b.0105230920.441e873e@posting.google.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: jim_esti@hotmail.com
  Subject: Re: Difficult SQL Statement
  In-Reply-To: <f0e3dc0b.0105230920.441e873e@posting.google.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