pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Renato De Giovanni <rdg@viafractal.com.br>
To: pgsql-sql@postgresql.org
Subject: Re: Difficult SQL Statement
Date: Wed, 30 May 2001 10:25:24 -0300
Message-ID: <3B14F4C4.EAEA74C6@viafractal.com.br> (raw)
References: <f0e3dc0b.0105230920.441e873e@posting.google.com>

> 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?

You can get the first 3 columns with one statement - the fourth column
should be calculated outside the query. Try this:

select d1.asmt_code,
       count(case when d1.status = 'PASSED' then 1 else NULL end) as passed,

       count(d1.amst_code) as total
from test_run d1
group by d1.asmt_code
order by d1.asmt_code

HTH,
--
Renato
Sao Paulo - SP - Brasil
rdg@viafractal.com.br





view thread (6+ messages)

Message-ID: <3B14F4C4.EAEA74C6@viafractal.com.br>
Permalink:  ../3B14F4C4.EAEA74C6@viafractal.com.br/
Also on:    postgresql.org/message-id/3B14F4C4.EAEA74C6@viafractal.com.br

 · 

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: rdg@viafractal.com.br
  Subject: Re: Difficult SQL Statement
  In-Reply-To: <3B14F4C4.EAEA74C6@viafractal.com.br>

* 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