Try following ( group by volume_name ) too. Since i don't have two volume same capacity so can't check. Let me know what worked with you.
select distinct cast((est_capacity_mb/600/100) as decimal(10,2)) as compratio, count(volume_name) as count from volumes where status = 'FULL' and stgpool_name like 'NASPOOL_L4' group by est_capacity_mb ,volume_name -----Original Message----- From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Raibeck Sent: Monday, June 02, 2008 2:43 PM To: [email protected] Subject: Re: [ADSM-L] yasq (yet another SQL question) The problem is that unless est_capacity_mb matches exactly between multiple volumes, the results of the "from" will return a single record from the VOLUMES table consisting of the est_capacity_mb field and the count (which is 1). After the math is done on the column to compute the ratio, you have the ratio plus the count of 1. If, after the math, more than one record has a matching ratio and count, DISTINCT will show only one of those records. So if two volumes have close (but not identical) est_capacity_mb fields such that the ratios round to the same value (e.g., 1.61 and 1.62 both round to 1.6) then you'll get a single output record showing a ratio of 1.6 and a count of 1. I am thinking that you'll need to just get the raw columnar data for each volume record, then feed it into another program (e.g., Perl), to get the calculations you want. Best regards, Andy Andy Raibeck IBM Software Group Tivoli Storage Manager Client Product Development Level 3 Team Lead Internal Notes e-mail: Andrew Raibeck/Tucson/[EMAIL PROTECTED] Internet e-mail: [EMAIL PROTECTED] IBM Tivoli Storage Manager support web page: http://www.ibm.com/software/sysmgmt/products/support/IBMTivoliStorageMan ager.html The only dumb question is the one that goes unasked. The command line is your friend. "Good enough" is the enemy of excellence. "ADSM: Dist Stor Manager" <[email protected]> wrote on 06/02/2008 11:08:34 AM: > 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.
