5

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
0
5

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
1
  • 1
    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.
    – Andriy M
    Aug 18 '15 at 7:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.