Try without distinct Goodl luck -----Original Message----- From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of Richard Rhodes Sent: Monday, June 02, 2008 2:09 PM To: ADSM-L@VM.MARIST.EDU Subject: [ADSM-L] yasq (yet another SQL question)
I'm trying to get a handle around the compression ratio on our 3494 tapes. I'm trying to generate a table showing each compression ratio and the number of volumes of that ratio. All the tapes in the lib are 60gb. It should look simething like this . . . ratio count ----- ----- 1.2 50 1.3 100 1.4 99 (etc, etc, etc) I've tried all kinds of sql variations, but can't get what I want. This attempt gives one line per volume with a ratio and count of 1. dsmadmc -se=${i} -id=<id> -password=<pwd> -noc -tab <<EOD select - cast((est_capacity_mb / 600 / 100) as decimal(2,1)) as compratio, - count(*) as count - from volumes - where status = 'FULL' - and stgpool_name like '%3494%' - group by est_capacity_mb EOD This attempt is close, but the counts are bad. dsmadmc -se=${i} -id=<id> -password=<pwd> -noc -tab <<EOD select - distinct cast((est_capacity_mb / 600 / 100) as decimal(2,1)) as compratio, - count(*) as count - from volumes - where status = 'FULL' - and stgpool_name like '%3494%' - group by est_capacity_mb EOD 0.8 1 0.9 1 0.9 2 1.0 1 1.1 1 1.2 1 1.3 1 1.4 1 1.5 1 1.6 1 1.7 1 1.8 1 1.9 1 2.0 1 2.1 1 2.2 1 2.3 1 2.4 1 2.7 1 2.8 1 2.9 1 3.0 1 3.1 1 Any help is appreciated!! Rick ----------------------------------------- The information contained in this message is intended only for the personal and confidential use of the recipient(s) named above. If the reader of this message is not the intended recipient or an agent responsible for delivering it to the intended recipient, you are hereby notified that you have received this document in error and that any review, dissemination, distribution, or copying of this message is strictly prohibited. If you have received this communication in error, please notify us immediately, and delete the original message.