Home » SQL & PL/SQL » SQL & PL/SQL » SUM up three rows into one and do it for the whole document. (merged)
SUM up three rows into one and do it for the whole document. (merged) [message #395200] Tue, 31 March 2009 10:59 Go to next message
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;
Re: SUM up three rows into one and do it for the whole document. (merged) [message #395205 is a reply to message #395200] Tue, 31 March 2009 12:22 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Sounds like you are missing a join. Not surprising with a 14 table query.
Previous Topic: Fetching changes for a given query
Next Topic: Declaring a Variable within a Function
Goto Forum:
  


Current Time: Sun Dec 11 00:14:13 CST 2016

Total time taken to generate the page: 0.14716 seconds