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:[EMAIL PROTECTED] On Behalf Of Ben Bullock Sent: Thursday, August 12, 2004 11:46 AM To: [EMAIL PROTECTED] 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
