# NetSuite Saved Search Summary Types, Group By and Summary Criteria > Source: https://netray.co/tools/netsuite-saved-search-summary-types-reference > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: Free Tools ## Short answer What each Summary Type returns, plus the formula patterns that do count distinct, conditional sums, latest value per group and HAVING-style filters. ## Category netsuite ## Erp NetSuite ## Audience - NetSuite administrator - Finance analyst - NetSuite developer ## Tool **Kind:** worksheet **Mode:** reference **Columns:** #### Part **Key:** part **Type:** text #### Summary type or pattern **Key:** summary_type **Type:** text #### Applies to **Key:** applies_to **Type:** text #### What it returns **Key:** what_it_returns **Type:** text #### Typical use **Key:** typical_use **Type:** text #### Formula pattern **Key:** formula_pattern **Type:** text #### Gotcha **Key:** gotcha **Type:** text **Rows:** **Part:** A: Summary types **Summary type:** Group **Applies to:** Any field or formula **What it returns:** One row per distinct value **Typical use:** Group by customer, item, month **Formula pattern:** Customer: Group **Gotcha:** Every non-aggregated column must be Group once any column has a summary type. **Part:** A: Summary types **Summary type:** Count **Applies to:** Any field **What it returns:** Number of result rows in the group **Typical use:** Lines per customer **Formula pattern:** Internal ID: Count **Gotcha:** Counts lines, not documents, unless Main Line is true. **Part:** A: Summary types **Summary type:** Sum **Applies to:** Numeric and currency fields **What it returns:** Total of the values **Typical use:** Revenue, quantity **Formula pattern:** Amount: Sum **Gotcha:** Without a Main Line filter, header and line amounts add together. **Part:** A: Summary types **Summary type:** Minimum **Applies to:** Numeric, currency, date and text fields **What it returns:** Lowest value **Typical use:** First order date **Formula pattern:** Date: Minimum **Gotcha:** Text minimum is alphabetical. **Part:** A: Summary types **Summary type:** Maximum **Applies to:** Numeric, currency, date and text fields **What it returns:** Highest value **Typical use:** Last order date, latest status **Formula pattern:** Date: Maximum **Gotcha:** Also the summary type to use on formulas that already contain an aggregate. **Part:** A: Summary types **Summary type:** Average **Applies to:** Numeric and currency fields **What it returns:** Mean of the values **Typical use:** Average line value **Formula pattern:** Amount: Average **Gotcha:** Averages lines, not orders; use a ratio formula for average order value. **Part:** B: Patterns **Summary type:** Count distinct documents **Applies to:** Formula (Numeric) **What it returns:** Number of distinct transactions **Typical use:** Orders per customer on a line-level search **Formula pattern:** COUNT(DISTINCT {internalid}) with Summary Type Maximum **Gotcha:** Plain Count on a line search overcounts multi-line orders. **Part:** B: Patterns **Summary type:** Conditional sum **Applies to:** Formula (Currency) **What it returns:** Total for rows meeting a condition **Typical use:** Billed vs open value in one row **Formula pattern:** CASE WHEN {status} = 'Billed' THEN {amount} ELSE 0 END with Summary Type Sum **Gotcha:** Return 0 in ELSE, not NULL, so totals show. **Part:** B: Patterns **Summary type:** Conditional count **Applies to:** Formula (Numeric) **What it returns:** Rows meeting a condition **Typical use:** Late lines per vendor **Formula pattern:** CASE WHEN {duedate} < {today} THEN 1 ELSE 0 END with Summary Type Sum **Gotcha:** Sum of 1s and 0s, not Count; Count would count every row. **Part:** B: Patterns **Summary type:** Last value of a date **Applies to:** Date field **What it returns:** Most recent date per group **Typical use:** Last purchase date per item **Formula pattern:** Date: Maximum **Gotcha:** Combine with Group on item; filter type first. **Part:** B: Patterns **Summary type:** Latest value per group **Applies to:** Formula (Currency) or (Text) **What it returns:** Value from the newest row in the group **Typical use:** Last price paid per item **Formula pattern:** MAX({rate}) KEEP (DENSE_RANK LAST ORDER BY {trandate}) with Summary Type Maximum **Gotcha:** Ties on the same date return the highest value; add {internalid} to ORDER BY to break ties. **Part:** B: Patterns **Summary type:** Earliest value per group **Applies to:** Formula (Currency) or (Text) **What it returns:** Value from the oldest row **Typical use:** First price quoted **Formula pattern:** MAX({rate}) KEEP (DENSE_RANK FIRST ORDER BY {trandate}) with Summary Type Maximum **Gotcha:** FIRST and LAST refer to the ORDER BY, not to MAX or MIN. **Part:** B: Patterns **Summary type:** Average order value **Applies to:** Formula (Currency) **What it returns:** Total divided by distinct documents **Typical use:** AOV per customer **Formula pattern:** SUM({amount}) / NULLIF(COUNT(DISTINCT {internalid}), 0) with Summary Type Maximum **Gotcha:** Use Main Line false and exclude tax and shipping lines if AOV should be goods only. **Part:** B: Patterns **Summary type:** Summary criteria (HAVING) **Applies to:** Criteria > Summary subtab **What it returns:** Only groups meeting a condition on an aggregate **Typical use:** Customers with Sum of Amount greater than 50,000 **Formula pattern:** Summary Type Sum, Field Amount, is greater than 50000 **Gotcha:** Standard criteria filter rows before grouping; Summary criteria filter groups after. **Part:** B: Patterns **Summary type:** Summary criteria on a formula **Applies to:** Criteria > Summary subtab **What it returns:** Groups meeting a formula condition **Typical use:** Items with more than 3 distinct vendors **Formula pattern:** Formula (Numeric) COUNT(DISTINCT {vendor}) with Summary Type Maximum, is greater than 3 **Gotcha:** The same aggregate-inside rule applies: pick Maximum. **Part:** B: Patterns **Summary type:** Main line handling **Applies to:** Transaction searches **What it returns:** Header rows only or line rows only **Typical use:** Counting orders vs summing lines **Formula pattern:** Criteria: Main Line is true (headers) or is false (lines) **Gotcha:** Leaving Main Line unset mixes header totals with lines and doubles sums. **Part:** B: Patterns **Summary type:** Group by a formula **Applies to:** Formula (Text) or (Date) **What it returns:** Buckets you define **Typical use:** Revenue by month **Formula pattern:** TO_CHAR({trandate}, 'YYYY-MM') with Summary Type Group **Gotcha:** Sort ascending on the formula; YYYY-MM sorts correctly as text. **Part:** B: Patterns **Summary type:** Group by age bucket **Applies to:** Formula (Text) **What it returns:** Aging bands **Typical use:** Open AR by 30-day bucket **Formula pattern:** CASE WHEN {today} - {duedate} <= 30 THEN '0-30' ELSE '30+' END with Summary Type Group **Gotcha:** Buckets sort as text; prefix with numbers to control order. **Part:** B: Patterns **Summary type:** Count of documents on a header search **Applies to:** Internal ID **What it returns:** Documents per group **Typical use:** Invoices per month **Formula pattern:** Main Line is true, Internal ID: Count **Gotcha:** Only correct when Main Line is true; otherwise use COUNT(DISTINCT). **Part:** B: Patterns **Summary type:** Signed quantity totals **Applies to:** Quantity on transaction lines **What it returns:** Net quantity **Typical use:** Units sold net of returns **Formula pattern:** CASE WHEN {type} = 'Credit Memo' THEN -ABS({quantity}) ELSE ABS({quantity}) END with Summary Type Sum **Gotcha:** Line signs vary by type; normalise with ABS and set the sign yourself. **Notes:** 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. ## Content **Intro:** 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. **Sections:** #### How summary searches work **Body:** 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. **Bullets:** - Dimensions: Group - Measures: Sum, Count, Minimum, Maximum, Average - Group filter: Criteria > Summary #### Worked example: top customers by distinct orders **Body:** 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 **Body:** 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 **Body:** 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. **Cta:** 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. **Faq:** #### How do I group by in a NetSuite saved search? **Answer:** 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? **Answer:** 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? **Answer:** 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? **Answer:** 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. ## Search terms netsuite saved search group by, netsuite saved search summary criteria, netsuite saved search formula count, netsuite formula max, netsuite saved search grouping, netsuite count distinct saved search, netsuite summary type --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json