Acumatica GI Aggregations — Group By, Having, and Rollups

Generic Inquiries can aggregate, but the designer hides the mechanism well enough that most builders never find the Having condition type and end up faking totals with Excel pivot tables downstream. That is a waste — GROUP BY runs in the database, where it belongs, and the result set that reaches the browser is already the summary you wanted.

Turning a results grid into a GROUP BY

Aggregation in the GI designer is not a separate mode; it is a property on individual result columns. Open the Results Grid tab, pick a numeric field, and set its Aggregate property to Sum, Count, Min, Max, or Avg. The moment any column carries an aggregate function, every other selected column that is not aggregated becomes an implicit GROUP BY key — exactly like plain SQL. Forget that and you get a confusing result: add ARInvoice.DocDate to a GI that sums CuryDocBal by customer, and suddenly you are grouped by customer and date, which usually was not the intent.

SELECT CustomerID, SUM(CuryDocBal) AS TotalBalance, COUNT(*) AS DocCount
FROM ARInvoice
WHERE Released = 1
GROUP BY CustomerID
HAVING SUM(CuryDocBal) > 10000

My practical rule: decide the grain first. Write down, in plain English, what one output row represents — "one row per customer", "one row per item per warehouse" — then select only the fields that belong to that grain plus the aggregates. Every extra non-aggregated field you drag in widens the grain whether you meant it to or not.

Where filters rows, Having filters groups

The Conditions tab in the GI designer lets you attach a condition to an aggregated field, and Acumatica is smart enough to emit it as a SQL HAVING clause rather than a WHERE clause when the field carries an aggregate. You do not pick this explicitly — it is inferred from whether the condition's field has an Aggregate function set. That inference is worth understanding because it explains a common bug: a condition on a non-aggregated field that logically "should" apply after grouping (say, filtering out customers whose average invoice is small) will not do what you expect unless the field itself is the aggregated one.

Aggregation performance and the fan-out interaction

Aggregated GIs are usually fast because the database does the heavy lifting and only summary rows cross the wire. The trap is combining aggregation with a join that fans out rows before the GROUP BY. Join SOOrder to both SOLine and SOOrderShipment in the same GI, then sum SOLine.OrderQty, and you will get numbers inflated by however many shipment rows exist per order — the join multiplies rows before the SUM ever runs. If you need two independent aggregates from two child tables, build two GIs (or two projections) and join their already-aggregated results, rather than aggregating over a fanned-out join.

Wrapping up

Treat GI aggregation like SQL because under the hood it is SQL: pick the grain, aggregate only the measures, use HAVING-style conditions on aggregated fields for post-group filters, and watch for fan-out joins quietly corrupting your sums before they ever reach the aggregate function.