I think u need one more tables (Payments) and u cand try sql like :



Select CustID, CustName, sum(o.values) TotalOrders , sum(p.values)
TotalPayments

From CUSTOMERS c Join ORDERS o on c.custid=o.custid

Join payments p on p.orderid=o.orderid

Where (sum(o.values) <> sum(p.values) )

Group by 1, 2



U can write sql more elaborate like this for all cases



Regards,

Paul



From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of majstoru
Sent: Monday, May 28, 2007 1:45 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Little complicated SQL request from guru



Hi,

I have two tables:

Customers (id, name)
History (cust_id, descript, in_money, out_money)

Here is a sample data:

Customers:
1 Cust_Name1
2 Cust_Name2
3 Cust_Name3

History
1 Order No 01 0.00 100.00
1 Order No 02 0.00 150.00
1 Order No 03 0.00 180.00
2 Order No 04 0.00 200.00
2 Order No 05 0.00 250.00
2 BankReport 1 200.00 0.00
1 BankReport 2 150.00 0.00

and I need SQL statement to get all order documents that are not
complet payed.

Cust_Name1 Order No 02 150.00 50.00
Cust_Name1 Order No 03 180.00 180.00
Cust_Name2 Order No 05 250.00 250.00

Last column is value which is represen the unclosed order by payment!

If I wasn't complicated in my description that is my SQL problem!

Thanks in advance...





[Non-text portions of this message have been removed]