Troubleshooting - How to COMPUTE sum without COMPUTE clause

Problem Description:

The following selection gives me the correct data using DISTINCT, but how do I get a sum of it, which is what I need?

Select distinct billtopayduedate, billtopayrefnumber, and bill.vendorreffullname, billtopayamountdue, assetaccountreffullname from bill nosync, bill pay nosync, billitemline nosync, item nosync where account to pay. billtopaytxnid = bill. tuned and bill.ispaid=false and bill. txnid = billitemline. txnid and BillItemLine.ItemLineItemRefListID = Item.ListID and assetaccountreffullname like '%inventory%.'

I want my SELECT statement to return one number: a sum of all the records in "Bill to Pay" where one or more corresponding line items in the expense line or item line is for inventory.

Solution:

In SQL, we do sum(column) and group by, for example:

SELECT Employee.PayrollInfoClassRefFullName, sum(Transaction.Amount) as Amount FROM Transaction, Employee where Transaction.EntityRefListID=Employee.ListID and Transaction.TxnType='Paycheck' and (Transaction.TxnDate >= {d'2017-01-01'} and Transaction.TxnDate < {d'2018-01-01'}) Group by Employee.PayrollInfoClassRefFullName

To total all the BillToPay amounts, you do the following:

Select sum(BillToPayAmountDue) as OpenAPBalance from BillToPay

To total all the BillToPay amounts involving stock (inventory) only and not expenses, you do the following:

Select sum(BillToPayAmountDue) as OpenInvtBalance from BillToPay, BillItemLine where BillToPay.BillToPayTxnID = BillItemLine.TxnID and BillItemLine.ItemLineSeqNo = 1