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 expression fails.

Author  Topic 

junes
Starting Member

12 Posts
Posted - 2008-10-28 : 09:57:10
I have written the following code and i am trying to add up the total column of the 4th line statement, but i do a SUM, the expression fails with a syntax error.

If i comment out statment 1,2,3 and do a SUM for the 4th line statement it works, but i need all statements.

select
sss.description STG_DESC,
sss.model STG_MODEL,
trunc((ssc.totalmb/1024),2) as STG_TOTAL_GB,
trunc( (svs.blocksize*svs.consumableblocks) / (1024*1024*1024),2 ) AS AVAILABLE_SPACE

....

How can i have the output of statement 1, 2, 3 and 4 show on one line.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-10-28 : 09:59:59
the posted part doesnt seem to have any errors. without seeing rest we cant suggest anything. can u post rest of query also please/
Go to Top of Page

junes
Starting Member

12 Posts
Posted - 2008-10-28 : 10:01:16
Here is the rest of the code

select
sss.description STG_DESC,
sss.model STG_MODEL,
trunc((ssc.totalmb/1024),2) as STG_TOTAL_GB,
trunc( (svs.blocksize*svs.consumableblocks) / (1024*1024*1024),2 ) AS CHESTER_USP_AVAILABLE_SPACE
from
mvc_storagevolumesummaryvw svs,
mvc_lunmappingvw lm,
mvc_protocolcontrollervw pc,
mvc_portcontrollermapvw pcm,
mvc_portsummaryvw psh, -- to get host port info
mvc_portsummaryvw pss, -- to get storage port info
mvc_cardsummaryvw cs,
mvc_hostsummaryvw hs,
mvc_storgaepoolsummaryvw sps,
mvc_storagesystemsummaryvw sss,
(select sc.storagesystemid,
sc.collectiontime,
sc.availablemb,
sc.provisionedmb,
sc.rawstoragemb,
sc.totalmb,
sc.availableports,
sc.connectedports,
sc.totalports
from mvc_storagesystemconfigvw sc
where sc.collectiontime = (select max(collectiontime) from mvc_storagesystemconfigvw where storagesystemid = sc.storagesystemid
)
) ssc
where
sss.storagesystemid = ssc.storagesystemid
and svs.storagevolumeid = lm.storage_volume_id(+)
and pc.id(+) = lm.storage_system_port_id
and pcm.controllerid(+) = pc.id
and pss.portid(+) = pcm.portid
and psh.wwn(+) = lm.initiator
and cs.cardid(+) = psh.containerid
and hs.hostid(+) = cs.containerid
and sps.storagepoolid(+) = svs.poolid
and svs.storagesystemname like '%Chester USP - CRXUSPSN%'
and sss.description NOT LIKE '%CRXUSPVSND%'
and sss.description NOT LIKE '%LONUSPSN%'
and sss.description NOT LIKE '%CROUSPSN%'
and pc.name is null
and sss.model='USP'
Go to Top of Page

junes
Starting Member

12 Posts
Posted - 2008-10-28 : 10:03:11
Here is the ouput

HDS Storage System Chester USP - CRXUSPSN USP 7363.94 10.41
HDS Storage System Chester USP - CRXUSPSN USP 7363.94 10.41
HDS Storage System Chester USP - CRXUSPSN USP 7363.94 10.41
..............
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-10-28 : 10:15:47
quote:
Originally posted by junes

Here is the rest of the code

select  
        sss.description STG_DESC,
        sss.model STG_MODEL,
        trunc((ssc.totalmb/1024),2) as STG_TOTAL_GB,
        sum(trunc( (svs.blocksize*svs.consumableblocks) / (1024*1024*1024),2 )) AS CHESTER_USP_AVAILABLE_SPACE
from
    mvc_storagevolumesummaryvw svs,
    mvc_lunmappingvw lm,
    mvc_protocolcontrollervw pc,
    mvc_portcontrollermapvw pcm,
    mvc_portsummaryvw psh,  -- to get host port info
    mvc_portsummaryvw pss,  -- to get storage port info
    mvc_cardsummaryvw cs,
    mvc_hostsummaryvw hs,
    mvc_storgaepoolsummaryvw sps,
    mvc_storagesystemsummaryvw sss,
       (select	sc.storagesystemid,
                sc.collectiontime, 
		sc.availablemb,
		sc.provisionedmb,
		sc.rawstoragemb,
                sc.totalmb,
                sc.availableports,
                sc.connectedports,
                sc.totalports
	from 	mvc_storagesystemconfigvw sc 
	where 	sc.collectiontime = (select max(collectiontime) from mvc_storagesystemconfigvw where storagesystemid = sc.storagesystemid
           )
        ) ssc
where
    sss.storagesystemid = ssc.storagesystemid
    and svs.storagevolumeid = lm.storage_volume_id(+)
    and pc.id(+) = lm.storage_system_port_id
    and pcm.controllerid(+) = pc.id
    and pss.portid(+) = pcm.portid
    and psh.wwn(+) = lm.initiator
    and cs.cardid(+) = psh.containerid
    and hs.hostid(+) = cs.containerid
    and sps.storagepoolid(+) = svs.poolid
    and svs.storagesystemname like '%Chester USP - CRXUSPSN%'
    and sss.description NOT LIKE '%CRXUSPVSND%'
    and sss.description NOT LIKE '%LONUSPSN%'
    and sss.description NOT LIKE '%CROUSPSN%'
    and pc.name is null
    and sss.model='USP'
group by sss.description,sss.model STG_MODEL,trunc((ssc.totalmb/1024),2)


modify like above and try
ALso please note that this is MS SQL SErver forum. if you're using any other db, please post in relevant forums to get specific syntax solutions related to your db
Go to Top of Page

junes
Starting Member

12 Posts
Posted - 2008-10-28 : 10:33:52
I have tried the following syntax, but it has failed with "ORA-00933: SQL command not properly ended"

group by sss.description,sss.model STG_MODEL,trunc((ssc.totalmb/1024),2)

I chnaged the syntax as follows and get the same error
group by sss.description STG_DESC,sss.model STG_MODEL,trunc((ssc.totalmb/1024),2) stg
group by STG_DESC, STG_MODEL, trunc((ssc.totalmb/1024),2)

Please can you help.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-10-28 : 10:36:56
Never sum a rounded value.
Always round a summed value.


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

junes
Starting Member

12 Posts
Posted - 2008-10-28 : 10:45:31
I have tried the following syntax, but it has failed with "ORA-00933: SQL command not properly ended"

group by sss.description,sss.model STG_MODEL,trunc((ssc.totalmb/1024),2)

I chnaged the syntax as follows and get the same error
group by sss.description STG_DESC,sss.model STG_MODEL,trunc((ssc.totalmb/1024),2) stg
group by STG_DESC, STG_MODEL, trunc((ssc.totalmb/1024),2)

Please can you help.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-10-28 : 10:46:19
quote:
Originally posted by junes

I have tried the following syntax, but it has failed with "ORA-00933: SQL command not properly ended"

group by sss.description,sss.model STG_MODEL,trunc((ssc.totalmb/1024),2)

I chnaged the syntax as follows and get the same error
group by sss.description STG_DESC,sss.model STG_MODEL,trunc((ssc.totalmb/1024),2) stg
group by STG_DESC, STG_MODEL, trunc((ssc.totalmb/1024),2)

Please can you help.

post in some oracle forums like www.dbforums.com
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-10-28 : 11:14:17
GROUP BY items can never have alias names for the expression!

group by sss.description,sss.model STG_MODEL ,trunc((ssc.totalmb/1024),2)


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-10-28 : 11:48:45
quote:
Originally posted by Peso

GROUP BY items can never have alias names for the expression!

group by sss.description,sss.model STG_MODEL ,trunc((ssc.totalmb/1024),2)


E 12°55'05.63"
N 56°04'39.26"

that was a stupid copy paste mistake
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site