Trying to to do a Sum(MAX) calculation in SQL Server

Hi All,

I'm trying to to do a SUM(MAX) calculation in SQL server and getting the follow error when executing the command

Quote:

---

Msg 130, Level 15, State 1, Line 6
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

---

I'm sure the error is caused by both

Code:

---

,SUM(MAX(convert(float,replace([AA_Now],'N/A','0')))) As [AA2_Now]

---

and

Code:

---

,SUM(MAX(convert(float,replace([STF_Now],'N/A','0')))) As [STF2_Now]

---

but have no idea how to rewrite it without causing the error.

Below is the full code.

Code:

---

SELECT  dbo.CCA_Merged.id, dbo.CCA_Merged.timeStamp, dbo.CCA_Merged.name, dbo.CCA_Merged.lN
            ,dbo.CCA_Merged.type, dbo.CCA_Merged.id2, dbo.CCA_Merged.aG
            ,dbo.CCA_Merged.regionId, dbo.CCA_Merged.sgcc       
            ,convert(float,replace([SLC_Today],'N/A','0')) As [SLC_Today]
            ,convert(float,replace([AA_Now],'N/A','0')) As [AA_Now]
            ,SUM(MAX(convert(float,replace([AA_Now],'N/A','0')))) As [AA2_Now]
            ,convert(float,replace([SLCO_Today],'N/A','0')) As [SLCO_Today]
            ,convert(float,replace([CABN_Today],'N/A','0')) As [CABN_Today]
            ,convert(float,replace([COF_Today],'N/A','0')) As [COF_Today]
            ,convert(float,replace([HT_Today],'N/A','0')) As [HT_Today]
            ,convert(float,replace(replace([CH_Today],'N/A','0'),'-','0')) As [CH_Today]
            ,convert(float,replace([SLC_Now],'N/A','0')) As [SLC_Now]
            ,convert(float,replace([SLCO_Now],'N/A','0')) As [SLCO_Now]
            ,convert(float,replace([SLC_Thirty],'N/A','0')) As [SLC_Thirty]
            ,convert(float,replace(replace([SLCO_Thirty],'N/A','0'),'-','0')) As [SLCO_Thirty]
            ,convert(float,replace([ACWT_Today],'N/A','0')) As [ACWT_Today]
            ,convert(float,replace([CQ_Now],'N/A','0')) As [CQ_Now]
            ,convert(float,replace([LCQ_Now],'N/A','0')) As [LCQ_Now]
            ,convert(float,replace([SLCH_Now],'N/A','0')) As [SLCH_Now]
            ,convert(float,replace([STF_Now],'N/A','0')) As [STF_Now]
            ,SUM(MAX(convert(float,replace([STF_Now],'N/A','0')))) As [STF2_Now]
            ,dbo.Sheet1$.AreaOfBusiness, dbo.Sheet1$.LifeCycleName, dbo.Sheet1$.LOB_name
FROM            dbo.Sheet1$ RIGHT OUTER JOIN
                        dbo.CCA_Merged ON dbo.Sheet1$.Skill_Name = dbo.CCA_Merged.lN   
Group by ROLLUP (stf_now) ,dbo.CCA_Merged.id, dbo.CCA_Merged.timeStamp, dbo.CCA_Merged.name, dbo.CCA_Merged.lN
                ,dbo.CCA_Merged.type, dbo.CCA_Merged.id2, dbo.CCA_Merged.aG ,dbo.CCA_Merged.regionId
                ,dbo.CCA_Merged.sgcc,AA_Now,SLC_Today,SLCO_Today,CABN_Today,COF_Today,HT_Today,CH_Today
                ,SLC_Now,SLCO_Now,SLC_Thirty,SLCO_Thirty,ACWT_Today,CQ_Now,LCQ_Now,SLCH_Now
                ,dbo.Sheet1$.AreaOfBusiness, dbo.Sheet1$.LifeCycleName, dbo.Sheet1$.LOB_name

---

I'm relatively new with SQL Server and any help would be greatly appreciated.

Thanks in advance