QBSync guide

Useful SQL queries for QuickBooks data

Once QuickBooks data is in a database, the most useful reports are a few lines of SQL. These examples use the table names QBSync creates; the ideas carry over to any QuickBooks schema with header and line tables.

Updated September 25, 2026 · 5 min read · By the Consus Solutions team

Open receivables by customer

sql

SELECT   customer_fullname,
         COUNT(*)              AS open_invoices,
         SUM(balanceremaining) AS balance_due
FROM     qb_invoice
WHERE    ispaid = 0
GROUP BY customer_fullname
ORDER BY balance_due DESC;

Overdue invoices

SQL Server syntax; in MySQL use CURDATE() instead of CAST(GETDATE() AS date).

sql

SELECT   refnumber, customer_fullname, duedate, balanceremaining
FROM     qb_invoice
WHERE    ispaid = 0
  AND    duedate < CAST(GETDATE() AS date)
ORDER BY duedate;

Unpaid bills by vendor

sql

SELECT   vendor_fullname,
         SUM(openamount) AS owed,
         MIN(duedate)    AS next_due
FROM     qb_bill
WHERE    ispaid = 0
GROUP BY vendor_fullname
ORDER BY owed DESC;

Sales by item

sql

SELECT   l.item_fullname,
         SUM(l.quantity) AS qty,
         SUM(l.amount)   AS revenue
FROM     qb_invoice_line l
JOIN     qb_invoice i ON i.txnid = l.txnid
GROUP BY l.item_fullname
ORDER BY revenue DESC;

Payments received by method

sql

SELECT   paymentmethod_fullname,
         COUNT(*)         AS payments,
         SUM(totalamount) AS received
FROM     qb_receivepayment
GROUP BY paymentmethod_fullname;

Checks written by payee and bank account

sql

SELECT   account_fullname AS bank_account,
         payeeentity_fullname AS payee,
         SUM(amount)          AS paid
FROM     qb_check
GROUP BY account_fullname, payeeentity_fullname
ORDER BY paid DESC;

Want these on a dashboard? See QuickBooks and Power BI.