Purpose of this query
This SuiteQL statement produces a combined dataset that shows both total expense by account and detailed vendor-level breakdowns for the selected date range. It returns each account’s total spending with a “TOTAL” row first, followed by “DETAIL” rows by vendor, making it ideal for Excel pivot tables, waterfall charts, or drill-down analysis. Users can change the hard-coded transaction date range, adjust what qualifies as an expense, and customize how vendors are displayed by modifying the query filters and joins. The query uses simple grouping and a UNION ALL to keep total rows at the top followed by detail rows, and includes handling for transactions with no vendor.
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.