Calculations using subquery

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
Press the little sum button in the menu bar of the query builder and it
gives you access to the calculation functions and does the Group by stuff
for you.

HTH;

Amy
 
Girts said:
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";
Click to expand...


The big issue here is that you mixed up the table name and
the alias. Try this.

SELECT T1.Nr, (T1.sum-(SELECT Sum(Dupe.Sum) FROM T1 as Dupe
WHERE (Dupe.Code="A")
And (Dupe.Nr=T1.Nr))) AS TotalSum
FROM T1
WHERE T1.Code="S"

Another serious issue is your use of reserved words as field
names. You should change the Sum and Code fields to
something else. (There are so many reserved words, many
people advocate avoiding any real words)
 
Hello !

Thanks, its works.
I just added IIF clause as query does not return any value if customer (Nr)
does not have any discount (Code="A").

It looks like:
SELECT T1.Nr, iif(isnull((T1.sum-(SELECT Sum(Dupe.Sum) FROM T1 as Dupe
WHERE (Dupe.Code="A") And (Dupe.Nr=T1.Nr)))), T1.sum, (T1.sum-(SELECT
Sum(Dupe.Sum) FROM T1 as Dupe
WHERE (Dupe.Code="A") And (Dupe.Nr=T1.Nr)))) AS TotalSum
FROM T1
WHERE T1.Code="S"

Marshall Barton said:
Girts said:
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";
Click to expand...


The big issue here is that you mixed up the table name and
the alias. Try this.

SELECT T1.Nr, (T1.sum-(SELECT Sum(Dupe.Sum) FROM T1 as Dupe
WHERE (Dupe.Code="A")
And (Dupe.Nr=T1.Nr))) AS TotalSum
FROM T1
WHERE T1.Code="S"

Another serious issue is your use of reserved words as field
names. You should change the Sum and Code fields to
something else. (There are so many reserved words, many
people advocate avoiding any real words)
Click to expand...
 
Back
Top