SUM up three rows into one and do it for the whole document. (merged) [message #395200] |
Tue, 31 March 2009 10:59  |
Junes
Messages: 5 Registered: October 2008
|
Junior Member |
|
|
Guys,
I need some advice please.
I am new to SQL scripting and i trying to sum up the following columns so that i have one line added togther for each hostname.
If i do the following:
select distinct
hs.hostname,
trunc(((hcap.used/1024/1024/1024) ),2) as USED_GB,
trunc(((hcap.free/1024/1024/1024) ),2) as FREE_GB,
trunc(((hcap.total/1024/1024/1024)),2) as TOTAL_GB
..... (same for the rest code below)
I get the following output
==========================
Hostname Used Free Total %
EMACROMSQ15 32.53 217.07 249.61 13
EMACROMSQ15 60.43 189.56 249.99 24
EMACROMSQ15 151.28 598.71 749.99 20
EMACROMSQ16 8.27 241.72 249.99 3
EMACROMSQ16 16.75 232.85 249.61 7
EMACROMSQ16 132.53 617.46 749.99 18
EMACROMSQ18 0.85 40.78 41.64 2
EMACROMSQ18 25.7 15.94 41.65 62
EMACROMSQ18 62.31 62.65 124.96 50
For some reason, when i sum the figures, it is doubleing or triple the sum. Which is very bizare.
select distinct
hs.hostname,
SUM ( trunc(((hcap.used/1024/1024/1024) ),2) ) as USED_GB,
SUM ( trunc(((hcap.free/1024/1024/1024) ),2) ) as FREE_GB,
SUM ( trunc(((hcap.total/1024/1024/1024)),2) ) as TOTAL_GB
..... (same for the rest code below)
But i am getting this output for some reason
============================================
Hostname Used Free Total
EMACROMSQ15 488.48 2010.68 2499.18
EMACROMSQ16 315.1 2184.06 2499.18
EMACROMSQ18 266.58 358.11 624.75
EMACROMSQ19 583.8 978.12 1561.95
I want to achive the following output
====================================
Hostname Used Free Total
EMACROMSQ15 244.24 1005.34 1249.59
EMACROMSQ16 157.55 1092.03 1249.59
EMACROMSQ18 88.86 119.37 208.25
EMACROMSQ19 194.6 326.04 520.65
The full code is below:
select distinct
hs.hostname,
SUM ( trunc(((hcap.used/1024/1024/1024) ),2) ) as USED_GB,
SUM ( trunc(((hcap.free/1024/1024/1024) ),2) ) as FREE_GB,
SUM ( trunc(((hcap.total/1024/1024/1024)),2) ) as TOTAL_GB
from
mvc_pathvw path,
mvc_subpathvw spath,
mvc_hostsummaryvw hs,
mvc_hostvolumesummaryvw hvs,
(select hc.volumeid,
hc.total,
hc.used,
hc.free,
hc.timestamp
from mvc_hostcapacityvw hc
where hc.timestamp = (select max(timestamp) from mvc_hostcapacityvw where volumeid = hc.volumeid)
) hcap,
mvc_cardsummaryvw cs,
mvc_portsummaryvw psh,
mvc_switchsummaryvw ss,
mvc_portsummaryvw pshs,
mvc_switchsummaryvw sss,
mvc_portsummaryvw pss,
mvc_storagesystemsummaryvw stgss,
mvc_portsummaryvw pssp,
mvc_storagevolumesummaryvw svs
where
spath.pathid(+) = path.pathid
and hs.hostid = path.hostid
and hvs.logicalvolumeid(+) = path.logicalvolumeid
and hcap.volumeid(+) = hvs.logicalvolumeid
and cs.cardid(+) = spath.hbacardid
and psh.containerid(+) = cs.cardid
and pshs.portid(+) = spath.hostswitchportid
and ss.switchid(+) = pshs.containerid
and pss.portid(+) = spath.systemswitchportid
and sss.switchid(+) = pss.containerid
and stgss.storagesystemid(+) = spath.storagesystemid
and pssp.portid(+) = spath.storagesystemportid
and svs.storagevolumeid(+) = spath.storagevolumeid
and stgss.storagesystemname is not null
and hvs.filesystemtype='NTFS'
and hvs.logicalvolumename is not null
and hs.hostname like 'EMACROMSQ1%'
GROUP BY hs.hostname
ORDER BY hs.hostname ASC;
|
|
|
|