Cannot Perform an Aggregate function on an expression containing an aggregate or a subquery

Question

Cannot Perform an Aggregate function on an expression containing an aggregate or a subquery

danny.delgado 65386

SSC-Addicted

Points: 439
More actions
November 30, 2015 at 1:52 am

#399400

Hi,
I have this SQL query that shows the output table below.
select en.NAME as ClusterName, ROUND(CAST(CPU_CORE_COUNT as FLOAT)*CPU_Hz*count(cast(hs.HOSTID as BIGINT))/800000000,0) as Hub_Capacity
from VPXV_HOSTS as hs WITH (NOLOCK,NOWAIT)
full join VPXV_ENTITY as en WITH (NOLOCK,NOWAIT) on hs.FARMID = en.id
where en.TYPE_ID = 3
group by en.Name, hs.CPU_CORE_COUNT, hs.CPU_Hz
ClustrName | Hub_Capacity
TOKvCL | 28
TOKvCL | 28
TOKvCL | 28
TOKvCL | 28
TOKvCL | 66
TOKvCL | 66
TOKvCL | 66
TOKvCL | 66
TOKvCL | 66
I would like to total the Hub_Capacity however I get an error "Cannot Perform an Aggregate function on an expression containing an aggregate or a subquery" if I append SUM before the ROUND
select en.NAME as ClusterName, SUM(ROUND(CAST(CPU_CORE_COUNT as FLOAT)*CPU_Hz*count(cast(hs.HOSTID as BIGINT))/800000000,0)) as Hub_Capacity
from VPXV_HOSTS as hs WITH (NOLOCK,NOWAIT)
full join VPXV_ENTITY as en WITH (NOLOCK,NOWAIT) on hs.FARMID = en.id
where en.TYPE_ID = 3
group by en.Name, hs.CPU_CORE_COUNT, hs.CPU_Hz
I cant figure out what is the correct syntax...appreciate if you can help/guide me on the correct Syntax. Thanks in advance.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

WITH Capacities AS (

select en.NAME as ClusterName, SUM(ROUND(CAST(CPU_CORE_COUNT as FLOAT)*CPU_Hz*count(cast(hs.HOSTID as BIGINT))/800000000,0)) as Hub_Capacity

from VPXV_HOSTS as hs WITH (NOLOCK,NOWAIT)

full join VPXV_ENTITY as en WITH (NOLOCK,NOWAIT) on hs.FARMID = en.id

where en.TYPE_ID = 3

group by en.Name, hs.CPU_CORE_COUNT, hs.CPU_Hz

)

SELECT SUM(Hub_Capacity) AS TotalCapacity

John

danny.delgado 65386 SSC-Addicted Points: 439 More actions · Answer 2

Hi John,

Thanks for your reply, I tried your suggestion but I still get the same error "Cannot Perform an aggregate function on an expression containing an aggregate or a subquery. Invalid column name 'Hub_Capacity' "

any other idea? Thanks in advance.

matak SSCrazy Points: 2674 More actions · Answer 3

Try this