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

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

You can only select into table variables. – HLGEM Sep 28 at 23:16 I would avoid scalar functions - in this case (if it's over only a few hundred thousand rows at most) it might out perform the triangular join, but for very large sets, the black boxes just don't perform. – Cade Roux Sep 29 at 0:49 I agree but regarding the prob and limitation of my knowledge, I can't imagine anything else to solve it easly.It doesn't work in one request...my first step was to find something works... – Guilhem Hoffmann Sep 29 at 7:49 has error : Msg 156, Level 15, State 1, Procedure getSumBSen, Line 20 Incorrect syntax near the keyword 'select'.

– Arash Sep 29 at 21:50.

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 Sen ) AS factor ON factor. Sen = t1.

Sen WHERE FCode = 81 AND DCode = 1 GROUP BY t1. Sen ) AS X.

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.

1 It was musa first proposal but after his test it doesn t seem to work , t1 his unknown GIN Subsystem query. I remove all commentd about it as it doesnot work...if you re sure let me know.... – Guilhem Hoffmann Sep 28 at 23:58 I think you need to do a similar triangular join as I did, because I get: Msg 4104, Level 16, State 1, Line 25 The multi-part identifier "t1. Sen" could not be bound.

– Cade Roux Sep 29 at 0:00 This is just a general idea. I can't really test the query without knowing the actual data model, which the OP did not tell us. – Branko Dimitrijevic Sep 29 at 0:04 BTW, I shouldn't need an INNER JOIN since SUM in the sub-query will produce exactly one row, so the Cartesian product will actually produce the same number of rows as in the "main" query.

– Branko Dimitrijevic Sep 29 at 0:10 2 @BrankoDimitrijevic It doesn't run - see the dummy table I made in my answer. The problem is the t1 - you can't use it in the definition of the subquery. You can use it in an implicit join of t2a to t1, but then the SUM won't be a triangular SUM.

– Cade Roux Sep 29 at 0:19.

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

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

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.