NetSuiteFree referencePDF, Excel, Word exportNetSuiteAll NetSuite tools

NetSuite Saved Search Summary Types, Group By and Summary Criteria

This reference covers the six Summary Types on a NetSuite saved search Results subtab and the formula patterns built on them, from count distinct to latest value per group. It also explains the Summary subtab on Criteria, which is the saved search equivalent of SQL HAVING.

Download:

Part	Summary type or pattern	Applies to	What it returns	Typical use	Formula pattern	Gotcha
A: Summary types	Group	Any field or formula	One row per distinct value	Group by customer, item, month	Customer: Group	Every non-aggregated column must be Group once any column has a summary type.
A: Summary types	Count	Any field	Number of result rows in the group	Lines per customer	Internal ID: Count	Counts lines, not documents, unless Main Line is true.
A: Summary types	Sum	Numeric and currency fields	Total of the values	Revenue, quantity	Amount: Sum	Without a Main Line filter, header and line amounts add together.
A: Summary types	Minimum	Numeric, currency, date and text fields	Lowest value	First order date	Date: Minimum	Text minimum is alphabetical.
A: Summary types	Maximum	Numeric, currency, date and text fields	Highest value	Last order date, latest status	Date: Maximum	Also the summary type to use on formulas that already contain an aggregate.
A: Summary types	Average	Numeric and currency fields	Mean of the values	Average line value	Amount: Average	Averages lines, not orders; use a ratio formula for average order value.
B: Patterns	Count distinct documents	Formula (Numeric)	Number of distinct transactions	Orders per customer on a line-level search	COUNT(DISTINCT {internalid}) with Summary Type Maximum	Plain Count on a line search overcounts multi-line orders.
B: Patterns	Conditional sum	Formula (Currency)	Total for rows meeting a condition	Billed vs open value in one row	CASE WHEN {status} = 'Billed' THEN {amount} ELSE 0 END with Summary Type Sum	Return 0 in ELSE, not NULL, so totals show.
B: Patterns	Conditional count	Formula (Numeric)	Rows meeting a condition	Late lines per vendor	CASE WHEN {duedate} < {today} THEN 1 ELSE 0 END with Summary Type Sum	Sum of 1s and 0s, not Count; Count would count every row.
B: Patterns	Last value of a date	Date field	Most recent date per group	Last purchase date per item	Date: Maximum	Combine with Group on item; filter type first.
B: Patterns	Latest value per group	Formula (Currency) or (Text)	Value from the newest row in the group	Last price paid per item	MAX({rate}) KEEP (DENSE_RANK LAST ORDER BY {trandate}) with Summary Type Maximum	Ties on the same date return the highest value; add {internalid} to ORDER BY to break ties.
B: Patterns	Earliest value per group	Formula (Currency) or (Text)	Value from the oldest row	First price quoted	MAX({rate}) KEEP (DENSE_RANK FIRST ORDER BY {trandate}) with Summary Type Maximum	FIRST and LAST refer to the ORDER BY, not to MAX or MIN.
B: Patterns	Average order value	Formula (Currency)	Total divided by distinct documents	AOV per customer	SUM({amount}) / NULLIF(COUNT(DISTINCT {internalid}), 0) with Summary Type Maximum	Use Main Line false and exclude tax and shipping lines if AOV should be goods only.
B: Patterns	Summary criteria (HAVING)	Criteria > Summary subtab	Only groups meeting a condition on an aggregate	Customers with Sum of Amount greater than 50,000	Summary Type Sum, Field Amount, is greater than 50000	Standard criteria filter rows before grouping; Summary criteria filter groups after.
B: Patterns	Summary criteria on a formula	Criteria > Summary subtab	Groups meeting a formula condition	Items with more than 3 distinct vendors	Formula (Numeric) COUNT(DISTINCT {vendor}) with Summary Type Maximum, is greater than 3	The same aggregate-inside rule applies: pick Maximum.
B: Patterns	Main line handling	Transaction searches	Header rows only or line rows only	Counting orders vs summing lines	Criteria: Main Line is true (headers) or is false (lines)	Leaving Main Line unset mixes header totals with lines and doubles sums.
B: Patterns	Group by a formula	Formula (Text) or (Date)	Buckets you define	Revenue by month	TO_CHAR({trandate}, 'YYYY-MM') with Summary Type Group	Sort ascending on the formula; YYYY-MM sorts correctly as text.
B: Patterns	Group by age bucket	Formula (Text)	Aging bands	Open AR by 30-day bucket	CASE WHEN {today} - {duedate} <= 30 THEN '0-30' ELSE '30+' END with Summary Type Group	Buckets sort as text; prefix with numbers to control order.
B: Patterns	Count of documents on a header search	Internal ID	Documents per group	Invoices per month	Main Line is true, Internal ID: Count	Only correct when Main Line is true; otherwise use COUNT(DISTINCT).
B: Patterns	Signed quantity totals	Quantity on transaction lines	Net quantity	Units sold net of returns	CASE WHEN {type} = 'Credit Memo' THEN -ABS({quantity}) ELSE ABS({quantity}) END with Summary Type Sum	Line signs vary by type; normalise with ABS and set the sign yourself.

Summary types are set per column on the Results subtab; summary filters live on Criteria > Summary. When a formula already contains an aggregate (COUNT, SUM, MAX ... KEEP), set the column to Maximum (or Minimum) so NetSuite treats it as a summary column. Test each pattern on a sandbox saved search before you share it.

Summary search totals wrong?

Send us the criteria and results setup and we will find the double count or grouping issue.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How summary searches work

A summary search is a GROUP BY query. Once you set a Summary Type on any Results column, every column needs one: Group for the dimensions, and Count, Sum, Minimum, Maximum or Average for the measures. Standard criteria filter rows before grouping; criteria on the Summary subtab filter the groups afterwards. Clicking a summary value in the results drills down to the rows behind it, which is the quickest way to check a total.

Dimensions: Group
Measures: Sum, Count, Minimum, Maximum, Average
Group filter: Criteria > Summary

Worked example: top customers by distinct orders

Transaction search, Criteria: Type is Sales Order, Main Line is false, Date within this fiscal year. Results: Name with Summary Type Group; Formula (Numeric) COUNT(DISTINCT {internalid}) with Summary Type Maximum; Amount with Summary Type Sum. Criteria > Summary: Summary Type Sum, Field Amount, is greater than 50000. The result is one row per customer with at least 50,000 of order lines, the number of distinct orders and the total line amount. Because Main Line is false, plain Count would have counted lines, not orders.

Common mistakes

The usual errors are double counting from an unset Main Line filter, Count used where distinct documents were wanted, Average on lines read as average order value, and a formula containing SUM or COUNT left with Summary Type Sum, which nests aggregates and fails. Another one is filtering on an aggregate in standard criteria: a filter like Amount greater than 50,000 there applies to each line, not to the customer's total.

How to check the result in the system

Drill into one group by clicking its summary value and compare the detail rows with what you expected: one row per line or per document. Then compare the total with a standard report such as Sales by Customer Summary for the same period. If they differ, check Main Line, tax and shipping lines and the transaction types included.

Frequently Asked Questions

How do I group by in a NetSuite saved search?

On the Results subtab, set Summary Type to Group on each column you want to group by, and set Sum, Count, Minimum, Maximum or Average on the measure columns. Every column must have a summary type once one does.

What are summary criteria in NetSuite?

The Summary subtab on the Criteria tab filters on aggregated values after grouping, like SQL HAVING. Choose a Summary Type, a field and a condition, for example Sum of Amount greater than 10,000, to keep only groups that pass.

How do I count distinct values in a saved search?

Add a Formula (Numeric) column with COUNT(DISTINCT {internalid}) and set its Summary Type to Maximum. Maximum tells NetSuite the formula is already an aggregate. Replace {internalid} with any field you want distinct counts of, such as {item} or {entity}.

Why does my saved search Count show more than the number of orders?

Transaction searches return one row per line unless Main Line is true. Count therefore counts lines. Either add Main Line is true for header-level counts, or use COUNT(DISTINCT {internalid}) with Summary Type Maximum on a line-level search.

If your team builds summary searches every week to answer the same questions, we can set up a private AI layer over NetSuite that answers them in plain English and shows the logic.

Talk to an Expert Explore More Free Tools