SUM up three rows into one and do it for the whole

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SUM up three rows into one and do it for the whole

Author

Topic

junes
Starting Member

12 Posts

Posted - 2009-03-31 : 10:42:09

Guys,
I need some help. 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.

How can i combine multiple lines for the same hostname and display only row with a total used, free and total.

What is currerntly being displayed
==================================

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

What i want to achieve
======================

Hostname Used Free Total %
EMACROMSQ15 244.24 1005.34 1249.59 20
EMACROMSQ16 157.55 1092.03 1249.59 13
EMACROMSQ18 88.86 119.37 208.25 43

Any advice provided will be most appericated.

Thanks

Junes

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-31 : 10:47:09

select Hostname,sum(Used),sum(Free),sum(total)
from <urtable> group by Hostname

junes
Starting Member

12 Posts

Posted - 2009-03-31 : 10:51:57

Thank you very much....

So simple i was missing "group by".

Always something simple....

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-31 : 11:06:44

np...good luck.

junes
Starting Member

12 Posts

Posted - 2009-03-31 : 11:38:36

vijayisonly need some more advice please.
For some reason, when i looked at the code more closely, its summing the figures up but it doubleing or triple the sum. Which is very bizare.

I should get this 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

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

The 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;

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-03-31 : 12:27:32

You should really be using ANSI joins.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources