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

Question

question

n3w2sql asked Oct 25, '12

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-2008 group-by aggregates

2

10 |1200

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

Kevin Feasel commented · Oct 25, 2012 at 01:10 PM

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 ·

n3w2sql commented · Oct 25, 2012 at 01:24 PM

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 ·

Answer 1 · 2012-10-25T13:31:30Z

Kevin Feasel answered Oct 25, '12

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

10 |1200

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

n3w2sql commented · Oct 26, 2012 at 09:53 AM

Thanks a lot that works perfectly and is exactly what I was trying to do. :)

0 ·

Answer 2 · 2014-07-02T08:04:17Z

vik answered Jul 2, '14

thank you

10 |1200

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

Answer 3 · 2015-02-05T20:47:15Z

DorisChamorro answered Feb 5, '15

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

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

Answer 4 · 2015-02-05T20:57:15Z

DorisChamorro answered Feb 5, '15

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

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

question