SQL : How can I use sub query in a query with grou

Question

How can I use sub query in a query with group by section?

I use SQL Server 2008 R2 AND Delphi 2010

I receive this error:

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

Like this query :

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 

group by t1.sen

Answer 1

You should be able to put your sub-query under FROM clause, following this general pattern:

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

Trying to "translate" your query, you'll probably get something similar to this:

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

Answer 2

Its the true way

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;

Answer 3

Try this:

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 ;

Answer 4

Without any warranty, you can try, if it doesn't work let me know, I'll delete my answer

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;

memento:

• Function creation

added, it won't let me actually fix the code because my edit is too short. So I had to write some more miscellaneous junk so that the code fix will be accepted.