What this query does and why it’s useful
This SuiteQL statement produces two types of rows for each expense account. The first row is a TOTAL for the account, and the following rows are DETAIL lines that show the amount by vendor. This is useful when you want a report that can be read top-down (totals first) while still supporting analysis by vendor.
Important: The date range is hard-coded (2025-12-01 through 2025-12-31) and should be updated before running the query. Users can also adjust what counts as an expense by modifying the filters that include expense accounts and expense line types.
SELECT
x.account_number,
x.account_name,
x.row_type,
x.vendor_code,
x.vendor_name,
x.amount_base_currency
FROM (
SELECT
a.acctnumber AS account_number,
a.fullname AS account_name,
'TOTAL' AS row_type,
NULL AS vendor_code,
'Total' AS vendor_name,
SUM(tl.amount) AS amount_base_currency,
0 AS sort_order
FROM transactionline tl
JOIN transaction t ON t.id = tl.transaction
JOIN account a ON a.id = tl.account
WHERE tl.posting = 'T'
AND (a.accttype = 'Expense' OR tl.accountinglinetype = 'Expense')
AND t.trandate >= TO_DATE('2025-12-01','YYYY-MM-DD')
AND t.trandate <= TO_DATE('2025-12-31','YYYY-MM-DD')
GROUP BY
a.acctnumber,
a.fullname
UNION ALL
SELECT
a.acctnumber AS account_number,
a.fullname AS account_name,
'DETAIL' AS row_type,
COALESCE(v.entityid, 'No Vendor') AS vendor_code,
COALESCE(v.companyname, v.entityid, 'No Vendor') AS vendor_name,
SUM(tl.amount) AS amount_base_currency,
1 AS sort_order
FROM transactionline tl
JOIN transaction t ON t.id = tl.transaction
JOIN account a ON a.id = tl.account
LEFT JOIN vendor v ON v.id = t.entity
WHERE tl.posting = 'T'
AND (a.accttype = 'Expense' OR tl.accountinglinetype = 'Expense')
AND t.trandate >= TO_DATE('2025-12-01','YYYY-MM-DD')
AND t.trandate <= TO_DATE('2025-12-31','YYYY-MM-DD')
GROUP BY
a.acctnumber,
a.fullname,
COALESCE(v.entityid, 'No Vendor'),
COALESCE(v.companyname, v.entityid, 'No Vendor')
) x
ORDER BY
x.account_number,
x.sort_order,
x.vendor_name;
Sample Output
How the query works (plain English)
Here is a simple breakdown of what the query is doing:
Filtering to posted expense activity in a date range
Both parts of the query use the same filter: posted lines only, expense classification, and a fixed transaction date range. Users should change the start and end dates to match the period they want.
Creating a total row per account
The first SELECT in the UNION ALL groups by account number and account name, and sums transaction line amounts. It labels each row as TOTAL and assigns sort_order = 0 so totals appear first.
Creating vendor detail rows per account
The second SELECT groups by account and vendor. It labels each row as DETAIL and assigns sort_order = 1 so the vendor rows appear after the total row for each account.
Handling transactions with no vendor
The vendor fields use COALESCE so that if a vendor is not present on the transaction, the output shows No Vendor instead of a blank value.
Combining totals and details into one output
UNION ALL merges the total rows and the detail rows into one dataset without removing duplicates, which is important because totals and details are both needed.
Sorting so results are easy to read
The ORDER BY sorts by account number, then shows the TOTAL row first (sort_order 0), then lists vendor detail rows alphabetically by vendor name.
Notes for customization
Users can adjust these parts of the query based on their needs.
Date range: update the two TO_DATE filters in both WHERE clauses.
Expense definition: adjust the condition that treats lines as expenses: (a.accttype = ‘Expense’ OR tl.accountinglinetype = ‘Expense’).
Vendor source: this query uses t.entity as the vendor link. If your data uses a different field for vendor on certain transactions, you may need to adjust the vendor join logic.