Try without distinct
Goodl luck
 

-----Original Message-----
From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On Behalf Of
Richard Rhodes
Sent: Monday, June 02, 2008 2:09 PM
To: ADSM-L AT VM.MARIST DOT 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.