# 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
``````
• You can do this by putting the `GROUP BY` in a subquery – yes, putting it in a subquery does make further calculation expressions simpler. But if you didn't use a subquery and had to repeat some expressions, that wouldn't be too much of a problem (not in this particular case, anyway). What's more important to this solution is the fact that you are using an outer join instead of an inner join. Avoiding an outer join would complicate the solution to a greater extent than avoiding a subquery would. Aug 18 '15 at 7:08