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.