Difficult SQL Statement

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

"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

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

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

Great!
If you're asking if the last column can be changed to THE_COUNT/TOTAL_NO*100
it can. Just change the -- as TOTAL_PERCENTAGE -- to
as "THE_COUNT/TOTAL_NO*100"

Tim

----- Original Message -----
From: "Jim Russell" <jim_esti@hotmail.com>
To: <tbarnard@povn.com>
Sent: Wednesday, May 30, 2001 12:16 PM
Subject: Re: [SQL] Difficult SQL Statement

Thanks for the ideas. Here is what I have come up with so far. I have
eliminated the need to temporary tables. The SQL statement works great.

select R1.ASMT_CODE, sum(case when R1.status = 'PASSED' then 1 else 0 end)
as PASSED, count(*) as TOTAL_NO, sum(case when R1.status = 'PASSED' then 1
else 0 end)/count(*)*100 as TOTAL_PRECENTAGE
from RESULT R1
group by R1.ASMT_CODE
order by R1.ASMT_CODE

Results in:

ASMT_CODE THE_COUNT TOTAL_NO TOTAL_PRECENTAGE
1 3 4 75
2 2 3 66.67
3 1 3 33.34
4 1 2 50

Back Ground stuff:

It would look perfect if the results looked like:
ASMT_CODE THE_COUNT TOTAL_NO THE_COUNT/TOTAL_NO*100
1 3 4 75
2 2 3 66.67
3 1 3 33.34
4 1 2 50

Source TAB (AKA: RESULTS table)
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

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

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