Calculate Account Balances w/SQLite
lazarus / 16-Databases

----------------------------------------------------------------------
Bert_Plitt · 2015-02-23 21:50

Can anyone help a novice user with a SQLite query?  I'll use an
abstract example to ask my question.

I have two tables, one called CUSTOMERS and one called LEDGER.  The
CUSTOMERS table has two fields of interest: CUST_ID, and
ACCT_BALANCE.  The LEDGER table has three fields of interest: CUST_ID,
DEBIT, and CREDIT.  Each record of the LEDGER consists of a CUST_ID
entry and either a DEBIT or CREDIT amount.  The CUSTOMERS table is a
list of all the customers in the database (CUST_ID), and the account
balance of each.

What would be the simplest query(s) that will total the credit minus
debits of each customer in the LEDGER table and enter the result into
the ACCT_BALANCE field of each corresponding customer in CUSTOMERS? 

I've tried various versions of qryCUST.SQL.Text:='SELECT ...' using
GROUP and SUM clauses, but cannot seem to get anything to work.  I'd
like some example Lazarus code for the query.  Any help appreciated.

----------------------------------------------------------------------
LacaK · 2015-02-24 05:59

If I understand correctly your question:

qryCust.SQL.Text := 'UPDATE CUSTOMERS SET ACCT_BALANCE=(SELECT
SUM(CREDIT-DEBIT) FROM LEDGER WHERE CUST_ID=CUSTOMERS.CUST_ID)';
qryCust.ExecSQL;

----------------------------------------------------------------------
Bert_Plitt · 2015-02-24 14:55

Thanks.  Looks simple.  The 'SET' command is what I was missing.