pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Difficult SQL Statement
6+ messages / 4 participants
[nested] [flat]

* Difficult SQL Statement
@ 2001-05-23 17:20  Jim <jim_esti@hotmail.com>
  0 siblings, 2 replies; 6+ messages in thread

From: jim_esti@hotmail.com @ 2001-05-23 17:20 UTC (permalink / raw)
  To: pgsql-sql

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?



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] Difficult SQL Statement
@ 2001-05-28 22:36  Tim Barnard <tbarnard@povn.com>
  parent: Jim <jim_esti@hotmail.com>
  1 sibling, 1 reply; 6+ messages in thread

From: Tim Barnard @ 2001-05-28 22:36 UTC (permalink / raw)
  To: Jim <jim_esti@hotmail.com>; +Cc: pgsql-general@postgresql.org

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
>




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Re: [SQL] Difficult SQL Statement
@ 2001-05-29 13:46  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Tim Barnard <tbarnard@povn.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Tom Lane @ 2001-05-29 13:46 UTC (permalink / raw)
  To: Tim Barnard <tbarnard@povn.com>; +Cc: Jim <jim_esti@hotmail.com>; pgsql-general@postgresql.org

"Tim Barnard" <tbarnard@povn.com> writes:
> To my thinking there's got to be a better way to do this whithout so many
> temporary tables.

In 7.1 you can frequently replace temp tables with subselect-in-FROM.
Cutting-and-pasting freely from your solution:

select ASMT_CODE, PASSED, TOTAL, PASSED::float4/TOTAL::float4
from
    (select ASMT_CODE, count(*) as TOTAL from RESULTS
     group by ASMT_CODE) as tmp1
  natural join
    (select ASMT_CODE, count(*) as PASSED from RESULTS
     where STATUS='PASSED' group by ASMT_CODE) as tmp2

(haven't tried this, but it looks right...)

This won't necessarily be a whole lot faster than the solution with
temp tables, but it's nice not to have to worry about dropping the
temp tables afterwards.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Re: [SQL] Difficult SQL Statement
@ 2001-05-29 14:50  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: Tom Lane @ 2001-05-29 14:50 UTC (permalink / raw)
  To: Tim Barnard <tbarnard@povn.com>; +Cc: Jim <jim_esti@hotmail.com>; pgsql-general@postgresql.org

I wrote:
> select ASMT_CODE, PASSED, TOTAL, PASSED::float4/TOTAL::float4
> from
>     (select ASMT_CODE, count(*) as TOTAL from RESULTS
>      group by ASMT_CODE) as tmp1
>   natural join
>     (select ASMT_CODE, count(*) as PASSED from RESULTS
>      where STATUS='PASSED' group by ASMT_CODE) as tmp2

BTW, although this is a fine example of how to avoid using temp tables,
it's not such a great solution to the original problem.  What happens
if there are no 'PASSED' entries at all for a given ASMT_CODE?  You
probably won't want that ASMT_CODE to disappear from your report ---
but it will disappear in the join.  We could fix this with a left join
and some hackery to deal with the resulting NULL values for PASSED,
but now things are getting ugly.  Time to try something different:

select ASMT_CODE, PASSED, TOTAL, PASSED::float4/TOTAL::float4 as PCT
from
    (select ASMT_CODE,
            count(*) as TOTAL,
            sum(CASE WHEN STATUS='PASSED' THEN 1 ELSE 0 END) as PASSED
     from RESULTS group by ASMT_CODE) as tmp1

Here we use the sub-select only as a way of labeling the count() and
sum() results so that we don't have to write and compute them twice.
You could write it as a simple one-level SELECT if you didn't mind
that redundancy.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Re: [SQL] Difficult SQL Statement
@ 2001-05-29 16:44  Tim Barnard <tbarnard@povn.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 6+ messages in thread

From: Tim Barnard @ 2001-05-29 16:44 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@postgresql.org

Thanks for the helpful tip!

Tim

----- Original Message ----- 
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Tim Barnard" <tbarnard@povn.com>
Cc: "Jim" <jim_esti@hotmail.com>; <pgsql-general@postgresql.org>
Sent: Tuesday, May 29, 2001 7:50 AM
Subject: Re: [GENERAL] Re: [SQL] Difficult SQL Statement 


> I wrote:
> > select ASMT_CODE, PASSED, TOTAL, PASSED::float4/TOTAL::float4
> > from
> >     (select ASMT_CODE, count(*) as TOTAL from RESULTS
> >      group by ASMT_CODE) as tmp1
> >   natural join
> >     (select ASMT_CODE, count(*) as PASSED from RESULTS
> >      where STATUS='PASSED' group by ASMT_CODE) as tmp2
> 
> BTW, although this is a fine example of how to avoid using temp tables,
> it's not such a great solution to the original problem.  What happens
> if there are no 'PASSED' entries at all for a given ASMT_CODE?  You
> probably won't want that ASMT_CODE to disappear from your report ---
> but it will disappear in the join.  We could fix this with a left join
> and some hackery to deal with the resulting NULL values for PASSED,
> but now things are getting ugly.  Time to try something different:
> 
> select ASMT_CODE, PASSED, TOTAL, PASSED::float4/TOTAL::float4 as PCT
> from
>     (select ASMT_CODE,
>             count(*) as TOTAL,
>             sum(CASE WHEN STATUS='PASSED' THEN 1 ELSE 0 END) as PASSED
>      from RESULTS group by ASMT_CODE) as tmp1
> 
> Here we use the sub-select only as a way of labeling the count() and
> sum() results so that we don't have to write and compute them twice.
> You could write it as a simple one-level SELECT if you didn't mind
> that redundancy.
> 
> regards, tom lane
> 




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Difficult SQL Statement
@ 2001-05-30 13:25  Renato De Giovanni <rdg@viafractal.com.br>
  parent: Jim <jim_esti@hotmail.com>
  1 sibling, 0 replies; 6+ messages in thread

From: Renato De Giovanni @ 2001-05-30 13:25 UTC (permalink / raw)
  To: pgsql-sql

> 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





^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2001-05-30 13:25 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-05-23 17:20 Difficult SQL Statement Jim <jim_esti@hotmail.com>
2001-05-28 22:36 ` Tim Barnard <tbarnard@povn.com>
2001-05-29 13:46   ` Tom Lane <tgl@sss.pgh.pa.us>
2001-05-29 14:50     ` Tom Lane <tgl@sss.pgh.pa.us>
2001-05-29 16:44       ` Tim Barnard <tbarnard@povn.com>
2001-05-30 13:25 ` Renato De Giovanni <rdg@viafractal.com.br>

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