Storage Pool Occupancy for each node report

[Benhayoune Khalid]

Hi all,

I'm new in TSM SQL and I have a problem with the following statement :

select  stgpools.stgpool_name as "STORAGE POOL", -
           stgpools.est_capacity_mb, -
           occupancy.node_name, -
           sum(occupancy.Physical_Mb) as "OCCUPANCY (MB)", -
           cast(sum(occupancy.Physical_Mb)/stgpools.est_capacity_mb*100 as
decimal(3,2)) as "%OCCUPED" -
from stgpools,occupancy -
where stgpools.stgpool_name = occupancy.stgpool_name -
group by stgpools.stgpool_name,stgpools.est_capacity_mb,occupancy.node_name

which returns 0 (wrong result as you can see below) for the column
"%OCCUPED" for all rows.

   STORAGE POOL: ARCH2004
EST_CAPACITY_MB: 5722020.0
      NODE_NAME: MON1
 OCCUPANCY (MB): 324.34
       %OCCUPED: 0.00

   STORAGE POOL: ARCH2004
EST_CAPACITY_MB: 5722020.0
      NODE_NAME: PC-MARSAOUI
 OCCUPANCY (MB): 10108.61
       %OCCUPED: 0.00

   STORAGE POOL: ARCH2004
EST_CAPACITY_MB: 5722020.0
      NODE_NAME: PROD1
 OCCUPANCY (MB): 230535.52
       %OCCUPED: 0.00

   STORAGE POOL: ARCH2004
EST_CAPACITY_MB: 5722020.0
      NODE_NAME: RAVEN2
 OCCUPANCY (MB): 0.11
       %OCCUPED: 0.00
...

Should I rewrite the SQL ?

Any suggestion ?

Thank you very much.