This shows the empty volumes in the pool.

select stgpool_name, count(*), sum(case when status='EMPTY' then 1 else 0
end) from volumes group by stgpool_name

When I added the join it runs forever as below, it is the select you want
but do not want the performance of.

select volumes.stgpool_name, count(*), sum(case when status='EMPTY' then 1
else 0 end), maxscratch from volumes, stgpools where volumes.stgpool_name =
stgpools.stgpool_name group by volumes.stgpool_name, maxscratch

Paul D. Seay, Jr.
Technical Specialist
Naptheon Inc.
757-688-8180


-----Original Message-----
From: David E Ehresman [mailto:deehre01 AT LOUISVILLE DOT EDU]
Sent: Tuesday, September 17, 2002 9:29 AM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Volume count and maxscratch


Can someone help me with a select that will list by storagepool name the
number of volumes in the storage pool and the maxscratch set for that pool?

Thanks,
David