pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDifficult SQL Statement
6+ messages / 4 participants
[nested] [flat]
* Difficult SQL Statement
@ 2001-05-23 17:20 Jim <jim_esti@hotmail.com>
2001-05-28 22:36 ` Re: [SQL] Difficult SQL Statement Tim Barnard <tbarnard@povn.com>
2001-05-30 13:25 ` Re: Difficult SQL Statement Renato De Giovanni <rdg@viafractal.com.br>
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 ‘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?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] Difficult SQL Statement
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 ` Re: Re: [SQL] Difficult SQL Statement Tom Lane <tgl@sss.pgh.pa.us>
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 ‘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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Re: [SQL] Difficult SQL Statement
2001-05-23 17:20 Difficult SQL Statement Jim <jim_esti@hotmail.com>
2001-05-28 22:36 ` Re: [SQL] Difficult SQL Statement Tim Barnard <tbarnard@povn.com>
@ 2001-05-29 13:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-05-29 14:50 ` Re: Re: [SQL] Difficult SQL Statement Tom Lane <tgl@sss.pgh.pa.us>
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-23 17:20 Difficult SQL Statement Jim <jim_esti@hotmail.com>
2001-05-28 22:36 ` Re: [SQL] Difficult SQL Statement Tim Barnard <tbarnard@povn.com>
2001-05-29 13:46 ` Re: Re: [SQL] Difficult SQL Statement Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-05-29 14:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-05-29 16:44 ` Re: Re: [SQL] Difficult SQL Statement Tim Barnard <tbarnard@povn.com>
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-23 17:20 Difficult SQL Statement Jim <jim_esti@hotmail.com>
2001-05-28 22:36 ` Re: [SQL] Difficult SQL Statement Tim Barnard <tbarnard@povn.com>
2001-05-29 13:46 ` Re: Re: [SQL] Difficult SQL Statement Tom Lane <tgl@sss.pgh.pa.us>
2001-05-29 14:50 ` Re: Re: [SQL] Difficult SQL Statement Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-05-29 16:44 ` Tim Barnard <tbarnard@povn.com>
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-23 17:20 Difficult SQL Statement Jim <jim_esti@hotmail.com>
@ 2001-05-30 13:25 ` Renato De Giovanni <rdg@viafractal.com.br>
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