SQL: ¿Cómo puedo usar la sub consulta en una consulta con grupo por sección?

¿Cómo puedo usar la sub consulta en una consulta grupo por sección?

Uso SQL Server 2008 R2 Y Delphi 2010

Recibo este error:

Cannot perform an aggregate function on an expression containing an aggregate or a sub query. 

Como esta consulta:

 select t1.sen, sum(t1.d1)as d1, sum(t1.d2)as d2, sum(t1.d1+t1.d2) as d_sum, Round((sum((1000*(t1.d1+t1.d2))/(9500-( select sum(t2.t_shab+t2.t_rooz) from tbl1 t2 where FCode=81 AND DCode=1 AND t2.sen<=t1.sen )))),1) as SSS from tbl1 t1 where FCode = 81 AND DCode = 1 

grupo por t1.sen

Sin ninguna garantía, puede intentar, si no funciona, hágamelo saber, eliminaré mi respuesta

 create function getSumBSen(@pfcode number, @pdcode number, @pSen number) returns number as begin declare @r number; select @r =sum(t2.t_shab + t2.t_rooz) from tbl1 t2 where t2.FCode = @pfcode and t2.DCode = @pdcode and t2.sen <= @pSen group by t2.FCode, t2.DCode; return (@r); end; select t1.sen, sum(t1.d1) as d1, sum(t1.d2) as d2, sum(t1.d1 + t1.d2) as d_sum, Round((sum((1000*(t1.d1+t1.d2))/(9500-getSumBSen(t1.FCode, t1.dcode, t1.sen)))),1) as SSS from tbl1 t1 where t1.FCode = 81 and t1.DCode = 1 group by t1.sen; 

recuerdo:

  • Creación de funciones

Además, no me permitirá corregir el código porque mi edición es demasiado corta. Así que tuve que escribir algo más de basura para que se aceptara la corrección de código.

Prueba esto:

 DECLARE @tbl1 AS TABLE ( FCode INT ,DCode INT ,sen INT ,d1 INT ,d2 INT ,t_shab INT ,t_rooz INT ) ; SELECT * FROM ( SELECT t1.sen ,SUM(t1.d1) AS d1 ,SUM(t1.d2) AS d2 ,SUM(t1.d1 + t1.d2) AS d_sum ,ROUND((SUM((1000 * (t1.d1 + t1.d2)) / (9500 - factor.factor))), 1) AS SSS FROM @tbl1 AS t1 INNER JOIN ( SELECT t1.sen ,SUM(t2.t_shab + t2.t_rooz) AS factor FROM @tbl1 AS t2 INNER JOIN @tbl1 AS t1 ON t1.FCode = 81 AND t1.DCode = 1 AND t2.FCode = 81 AND t2.DCode = 1 AND t2.sen <= t1.sen GROUP BY t1.sen ) AS factor ON factor.sen = t1.sen WHERE FCode = 81 AND DCode = 1 GROUP BY t1.sen ) AS X ; 

Debería poder colocar su subconsulta debajo de la cláusula FROM , siguiendo este patrón general:

 SELECT TABLE1.ID, SUM(TABLE1.A), ROUND(SUM(T2.B1), 2) FROM TABLE1, (SELECT SUM(B) B1 FROM TABLE2 WHERE ...) T2 GROUP BY TABLE1.ID 

Al tratar de "traducir" su consulta, probablemente obtendrá algo similar a esto:

 select t1.sen, sum(t1.d1)as d1, sum(t1.d2)as d2, sum(t1.d1+t1.d2) as d_sum, Round((sum((1000*(t1.d1+t1.d2))/(9500-( t2a.s )))),1) as SSS from tbl1 t1, ( select sum(t2.t_shab+t2.t_rooz) s from tbl1 t2 where FCode=81 AND DCode=1 AND t2.sen<=t1.sen ) t2a where FCode = 81 AND DCode = 1 group by t1.sen 

Es la verdadera manera

 create function getSumBSen2(@pfcode INT, @pdcode INT, @pSen INT) returns int as begin declare @r int; select @r= sum(t2.t_shab + t2.t_rooz) from tbl1 t2 where t2.FCode = @pfcode and t2.DCode = @pdcode and t2.sen <= @pSen; return (@r); end; GO select t1.sen, sum(t1.d1) as d1, sum(t1.d2) as d2, sum(t1.d1 + t1.d2) as d_sum, Round((sum((1000*(t1.d1+t1.d2)+0.01)/(9500-(dbo.getSumBSen2(t1.FCode, t1.DCode, t1.sen))))),1) as SSS from tbl1 t1 where t1.FCode = 81 and t1.DCode = 1 group by t1.sen;