Knowledgebase: Tutorials
[QODBC-Desktop] Troubleshooting - How to COMPUTE sum without COMPUTE clause
Posted by Jack - QODBC Support on 09 March 2016 01:09 PM

Troubleshooting - How to COMPUTE sum without COMPUTE clause

Problem Description:

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

select distinct billtopayduedate, billtopayrefnumber,bill.vendorreffullname, billtopayamountdue, assetaccountreffullname from bill nosync, billtopay nosync, billitemline nosync, item nosync where billtopay.billtopaytxnid = bill.txnid 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 expenseline or itemline 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 simply do:

Select sum(BillToPayAmountDue) as OpenAPBalance from BillToPay

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

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

(0 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).