Re: Select statement help.

I took your code and added a "max" function.
Not sure if this is the best SQL way to deal with it but it makes the "group
by" happy and seems to work:


select max(st.STGPOOL_NAME) as "STGPOOL",  max(st.maxscratch) as
"MAXSCRATCH", (count(vo.VOLUME_NAME))as "# of tapes" from volumes vo,
stgpools st where vo.DEVCLASS_NAME like 'LTO%' and
vo.STGPOOL_NAME=st.STGPOOL_NAME group by vo.STGPOOL_NAME




-----Original Message-----
From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On Behalf Of 
Ben
Bullock
Sent: Thursday, August 12, 2004 11:46 AM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Select statement help.


        Ok, this should be simple, but it's driving me nuts.

        I have 2 simple select statements that I'm trying to combine
into 1 but it's not working.

        Here are the 2 scripts and their output:

tsm: TSMSERV1A>select STGPOOL_NAME, MAXSCRATCH from stgpools where
DEVCLASS like '3590DEV'

STGPOOL_NAME            MAXSCRATCH
------------------     -----------
A_COPYPOOL                     200
A_TAPEPOOL                     500
COL_FS_TAPEPOOL                500
COPYPOOL                      1000
DB_TAPEPOOL                   1000
I_TAPEPOOL                     500

tsm: TSMSERV1A>select STGPOOL_NAME, (count(VOLUME_NAME))as "# of tapes"
from volumes where DEVCLASS_NAME like '3590DEV' group by STGPOOL_NAME


STGPOOL_NAME            # of tapes
------------------     -----------
A_COPYPOOL                       7
A_TAPEPOOL                       9
COL_FS_TAPEPOOL                222
COPYPOOL                       401
DB_TAPEPOOL                    262
I_TAPEPOOL                     106

        I would simply like to combine them into 1 script so that it is
easier to compare the Maxscratch value with the actual number of tapes
being used.

        Trying various things like:

        select vo.STGPOOL_NAME, st.STGPOOL_NAME,
(count(vo.VOLUME_NAME))as "# of tapes", st.MAXSCRATCH from volumes vo,
stgpools st where vo.DEVCLASS_NAME like '3590DEV' and
vo.STGPOOL_NAME=st.STGPOOL_NAME group by vo.STGPOOL_NAME

        But it's not working... any help?

Thanks,
Ben