How to use a group by in inner join I have Two tables:

Table 1

ID   totalamount
---  -----------
100  1000
101  500


Table 2

ID   Individualamount
---  ----------------
100  500
100  300
100  100
101  200
101  300


I Need a result which shows

ID   TotalAmount  sum(Individualamount)  difference (TA-IA)  % of diff 
---  -----------  ---------------------  ------------------  ---------
100  1000         900                    100                 10
101  500          500                    0                   0
You can do this by putting the GROUP BY in a subquery. Here is a solution:

CREATE TABLE #1 (ID int, TotalAmount decimal(20,2));
CREATE TABLE #2 (ID int, Individualamount decimal(20,2));

INSERT #1 VALUES (100, 1000), (101, 500);
INSERT #2 VALUES (100, 500), (100, 300), (100, 100), (101, 200), (101, 300);

SELECT a.ID
, a.TotalAmount
, c.[Sum(IndividualAmount)]
, d.[Diffence(TA-IA)]
, e.[%of diff]
FROM #1 a
LEFT JOIN
(
    SELECT b.ID, [Sum(IndividualAmount)] = SUM(b.IndividualAmount)
    FROM #2 b
    GROUP BY b.ID
) c
    ON c.ID = a.ID
CROSS APPLY
(
    SELECT [Diffence(TA-IA)] = a.TotalAmount - c.[Sum(IndividualAmount)]
) d
CROSS APPLY
(
    SELECT [%of diff] = 100.0 * [Diffence(TA-IA)] / NULLIF(a.TotalAmount, 0)
) e;


Results:

ID   TotalAmount  Sum(IndividualAmount)  Diffence(TA-IA)    %of diff
100      1000.00                 900.00           100.00   10.000000
101       500.00                 500.00             0.00    0.000000