question

n3w2sql avatar image
n3w2sql asked

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

I am trying to do a sum and count within my case statement but I am getting the error of Cannot perform an aggregate function on an expression containing an aggregate or a subquery. Do I need to create another table to get this to work? my code is below. Thanks SELECT m.[Class], isnull([Q1Ontime],0)[Q1Ontime], isnull([Q1Late],0)[Q1Late], isnull(CONVERT(DECIMAL(5,0),([Q1Ontime]*1.0)/([Q1Ontime]+[Q1Late]) * 100),0)Q1Success, isnull([Q2Ontime],0)[Q2Ontime], isnull([Q2Late],0)[Q2Late], isnull(CONVERT(DECIMAL(5,0),([Q2Ontime]*1.0)/([Q2Ontime]+[Q2Late]) * 100),0)Q2Success, isnull([Q3Ontime],0)[Q3Ontime], isnull([Q3Late],0)[Q3Late], isnull(CONVERT(DECIMAL(5,0),([Q3Ontime]*1.0)/([Q3Ontime]+[Q3Late]) * 100),0)Q3Success, isnull([Q4Ontime],0)[Q4Ontime], isnull([Q4Late],0)[Q4Late], isnull(CONVERT(DECIMAL(5,0),([Q4Ontime]*1.0)/([Q4Ontime]+[Q4Late]) * 100),0)Q4Success FROM( SELECT Dept [Class], SUM(CASE WHEN Q = 1 THEN COUNT(POLICYNUM)-SUM(Late) END) [Q1Ontime], SUM(CASE WHEN Q = 1 THEN sum(late) END) AS [Q1Late], SUM(CASE WHEN Q = 2 THEN COUNT(POLICYNUM)-SUM(Late) END) [Q2Ontime], SUM(CASE WHEN Q = 2 THEN sum(late) END) AS [Q2Late], SUM(CASE WHEN Q = 3 THEN COUNT(POLICYNUM)-SUM(Late) END) [Q3Ontime], SUM(CASE WHEN Q = 3 THEN sum(late) END) AS [Q3Late], SUM(CASE WHEN Q = 4 THEN COUNT(POLICYNUM)-SUM(Late) END) [Q4Ontime], SUM(CASE WHEN Q = 4 THEN sum(late) END) [Q4Late] FROM lee_em.dbo.[allt] where Inceptyear ='2012' GROUP BY Dept )m INNER JOIN ( SELECT [Dept] AS [Class], '' [Q1Success], '' [Q2Success], '' [Q3Success], '' [Q4Success] FROM lee_em.dbo.[allt] GROUP BY [Dept] )n ON n.[Class] = m.[Class]
sql-server-2008group-byaggregates
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

It's a little hard to tell from the query, but what are POLICYNUM and Late defined as? Are they descriptive fields relating to a particular record (e.g., POLICYNUM as a varchar and Late as a bit flag), or are they counts of the numbers of entities which are covered (policynum) and the number which are late (Late)?
0 Likes 0 ·
Policynum is varchar and late is int. Table allt has a list of policy numbers (policynum)and the late field contains either a 0 or 1 to show if policynum is late. I hope that helps explain it. Thanks
0 Likes 0 ·
Kevin Feasel avatar image
Kevin Feasel answered
The error is saying that you can't nest aggregated functions--in other words, you can't have a COUNT or a SUM within a SUM. Fortunately, there are ways around it. In this case, you can convert your aggregate into something like: SELECT Dept [Class], SUM(CASE WHEN Q = 1 THEN 1 ELSE 0 END) - SUM(case when Q = 1 then Late else 0 end) [Q1Ontime], SUM(CASE WHEN Q = 1 and Late = 1 then 1 else 0 end) AS [Q1Late], SUM(CASE WHEN Q = 2 THEN 1 ELSE 0 END) - SUM(case when Q = 2 then Late else 0 end) [Q2Ontime], SUM(CASE WHEN Q = 2 and Late = 1 then 1 else 0 end) AS [Q2Late], SUM(CASE WHEN Q = 3 THEN 1 ELSE 0 END) - SUM(case when Q = 3 then Late else 0 end) [Q3Ontime], SUM(CASE WHEN Q = 3 and Late = 1 then 1 else 0 end) AS [Q3Late], SUM(CASE WHEN Q = 4 THEN 1 ELSE 0 END) - SUM(case when Q = 4 then Late else 0 end) [Q4Ontime], SUM(CASE WHEN Q = 4 and Late = 1 then 1 else 0 end) AS [Q4Late] FROM lee_em.dbo.[allt] where Inceptyear ='2012' GROUP BY Dept In this case, we replace COUNT with SUM(1) to get a count when a condition is true (Q = 1, etc.). Because Late is an integer instead of a bit, you can sum it up directly. Running this should give you the correct results (or at least the results you would have gotten with your original query) without an error.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks a lot that works perfectly and is exactly what I was trying to do. :)
0 Likes 0 ·
vik avatar image
vik answered
thank you
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DorisChamorro avatar image
DorisChamorro answered
Hi, please your help: SUM(((sum(a11.IngresosFinancieros*a11.TipoCambio) + (sum(a11.transferencia * a11.tipocambio)) + (sum((a11.GastosFinancieros * a11.TipoCambio)*-1)) + (sum(a11.ingresoscomisiones * a11.tipocambio)+sum(a11.ingresosservicios * a11.tipocambio)+sum(a11.utilidadCambio * a11.tipoCambio)) + (sum((a11.gastosdirectos * a11.tipoCambio)*-1)-sum((a11.gastosindirectos* a11.tipocambio)*-1)) + (sum(a11.recuperaciones) + sum((a11.Provision_Req_Esp * a11.tipoCambio)*-1))))) AS MARGENCONTRIBUCION1 ------------------------------------------ Msg 130, Level 15, State 1, Line 14 Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DorisChamorro avatar image
DorisChamorro answered
--------my query complet-------------------------------------------------- select max(a11.cTiempo)Tiempo, max(a12.Fecha) Fecha, case when a16.cZona IN (1,13,14,17,4) then 1 when a16.cZona IN (12,18,8) then 2 else 0 end cRed, max(case when a16.cZona IN (1,13,14,17,4) then 'RED I' when a16.cZona IN (12,18,8) then 'RED II' else 'ZONA OTROS' end) Red, max(a16.Zona) zona, max(a16.Agencia ) Agencia, max(case when a13.codigoProducto_Cf in ('0149','0062','0072','0148','0016','0031','0169','6815','0012') then '01' when a13.codigoProducto_Cf in ('0078','0004','0054') then '02' when a13.codigoProducto_Cf in ('0013','0014') then '03' when CodigoClaseProducto in ('0001','0002','0015','0016','0017') THEN '01' when a13.CodigoClaseProducto in ('0003') then '03' else '04' end )IDClaseProducto3Minorista, max(case when a13.codigoProducto_Cf in ('0149','0062','0072','0148','0016','0031','0169','6815','0012') then 'ACTIVOS' when a13.codigoProducto_Cf in ('0078','0004','0054') then 'PASIVOS' when a13.codigoProducto_Cf in ('0013','0014') then 'CONTINGENTES' when CodigoClaseProducto in ('0001','0002','0015','0016','0017') THEN 'ACTIVOS' when a13.CodigoClaseProducto in ('0003') then 'CONTINGENTES' else 'OTROS' end) TClaseProducto3Minorista, max(case when a17.cArgumentoCore in ('0072','0148') then 'TARJETA' when a17.cArgumentoCore in ('0149','0062','0072','0148','0031','0016','0169','0062','0012','0078','0004','0054','0170','0013','0014') then (upper(a17.descripcionLarga)) else 'OTROS' end) Producto, **------here is the problem-------------** (((sum(a11.IngresosFinancieros*a11.TipoCambio) + (sum(a11.transferencia * a11.tipocambio)) + (sum((a11.GastosFinancieros * a11.TipoCambio)*-1)) + (sum(a11.ingresoscomisiones * a11.tipocambio)+sum(a11.ingresosservicios * a11.tipocambio)+sum(a11.utilidadCambio * a11.tipoCambio)) + (sum((a11.gastosdirectos * a11.tipoCambio)*-1)-sum((a11.gastosindirectos* a11.tipocambio)*-1)) + (sum(a11.recuperaciones) + sum((a11.Provision_Req_Esp * a11.tipoCambio)*-1))))) AS MARGENCONTRIBUCION1 from RenBaseAG03 a11 left outer join Tiempo a12 on a11.cTiempo = a12.cTiempo left outer join Producto a13 on a11.cProducto = a13.cProducto left outer join (select f1.cFuncionario,f1.CodigoFuncionario, f2.BancaAreaNegocio,f2.cBancaAreaNegocio from funcionarios f1 inner join funcionarios f2 on f1.cFuncionario=f2.cFuncionario and f2.FechaFinVigencia is null) a15 on a11.cFuncionarioCorte = a15.cFuncionario left outer join Agencia a16 on a11.cAgenciaFuncionario = a16.cAgencia left outer join (select cArgumentoCore, DescripcionLarga from Leyendas where cTablaCore ='0051') a17 on a11.codigoProducto_Cf = a17.cArgumentoCore where a12.FECHA < REPLACE(CONVERT(VARCHAR(10),GETDATE(),102),'.','-') and a11.cBancaAreaNegocio = 112 -- Division Banca Minorista and a11.cTiempo = 3530 -- Fecha 31 de Agosto del 2014 and a16.cZona = 1 --- GABRIELA DIESELDORFF and a16.cAgencia = 82 -- Agencia Tacna and a13.codigoProducto_Cf in ('0149','0062','0072','0148','0016','0031','0169','6815','0012','0078','0004','0054','0013','0014')---Activos,Pasivos, Contingentes group by a11.cTiempo,a16.cAgencia,a16.Agencia,a17.cArgumentoCore,case when a13.codigoProducto_Cf in ('0149','0062','0072','0148','0016','0031','0169','6815','0012') then '01' when a13.codigoProducto_Cf in ('0078','0004','0054') then '02' when a13.codigoProducto_Cf in ('0013','0014') then '03' when CodigoClaseProducto in ('0001','0002','0015','0016','0017') THEN '01' when a13.CodigoClaseProducto in ('0003') then '03' else '04' end,case when a13.codigoProducto_Cf in ('0149','0062','0072','0148','0016','0031','0169','6815','0012') then 'ACTIVOS' when a13.codigoProducto_Cf in ('0078','0004','0054') then 'PASIVOS' when a13.codigoProducto_Cf in ('0013','0014') then 'CONTINGENTES' when CodigoClaseProducto in ('0001','0002','0015','0016','0017') THEN 'ACTIVOS' when a13.CodigoClaseProducto in ('0003') then 'CONTINGENTES' else 'OTROS' end,case when a16.cZona IN (1,13,14,17,4) then 1 when a16.cZona IN (12,18,8) then 2 else 0 end,a11.FLAGCAPTACIONES, ---------------No group by---------like contraction---no extendent -------------------------------- a11.IngresosFinancieros,a11.transferencia,a11.GastosFinancieros,a11.ingresoscomisiones ,ingresosservicios ,a11.utilidadCambio,a11.gastosdirectos ,a11.gastosindirectos,A11.Recuperaciones,a11.Provision_Req_Esp,A11.TipoCambio order by 7
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.