G
Guest
Hi !
I have one table (T1) with customer (Nr), amount type (Code), amount (Sum).
Values for code: S - amount, A- discount for amount.
How to create query what calculate [amount - SUM(of discounts)] for each
customer ?
Example:
Nr |Code|Sum
111|S |10
111|A |1
111|A |2
333|S |30
222|S |20
Result should look like:
Nr |Sum
111|7
333|30
222|20
I think the right way is to use subquery but cannot find the way how to link
it by [Nr] with main query.
SELECT T1.Nr, (t1.sum-(SELECT Sum(T1.Sum) FROM T1 as Dupe WHERE
(T1.Code="A") and (Dupe.Nr=T1.Nr))) AS TotalSum
FROM T1
WHERE (T1.Code)="S";
Thanks,
Girts
I have one table (T1) with customer (Nr), amount type (Code), amount (Sum).
Values for code: S - amount, A- discount for amount.
How to create query what calculate [amount - SUM(of discounts)] for each
customer ?
Example:
Nr |Code|Sum
111|S |10
111|A |1
111|A |2
333|S |30
222|S |20
Result should look like:
Nr |Sum
111|7
333|30
222|20
I think the right way is to use subquery but cannot find the way how to link
it by [Nr] with main query.
SELECT T1.Nr, (t1.sum-(SELECT Sum(T1.Sum) FROM T1 as Dupe WHERE
(T1.Code="A") and (Dupe.Nr=T1.Nr))) AS TotalSum
FROM T1
WHERE (T1.Code)="S";
Thanks,
Girts