Hibernate 7 Aggregate Functions: COUNT, SUM, AVG, GROUP BY/HAVING, and the Null-vs-Zero Trap

Say you need one number out of a table — how many orders shipped today, the average price in a category, the most expensive item in stock. The obvious way to get it in Java is to load the matching rows into a List and add them up yourself. That works at a hundred rows. At a million, it is the reason your service starts throwing OutOfMemoryError for a question the database could have answered by reading its own index.

This is the beginner-through-expert guide to Hibernate’s aggregate functions — count, sum, avg, min, max, plus GROUP BY/HAVING, typed record projections, the Criteria API equivalent, and HQL window functions — built and verified against a real Spring Boot project rather than described from memory. Every code block below links to the file it came from and every console line was copied from a committed, real test run, in the hibernate-demo companion repository.

Versions used in this post. Hibernate ORM 7.4.5.Final, Jakarta Persistence 3.2, Spring Boot 4.1.1, JDK 25. All output was captured from real test runs against these exact versions — where an older post (including an earlier version of this one) claimed something different, it is corrected below with what javap or an actual run showed instead.

Why “fetch the rows and add them up in Java” stops working

Picture a reporting screen that shows the average order value for the last quarter. The tempting first implementation loads every matching Order entity, streams over the list, and calls average(). It works fine in a demo with twenty rows. It keeps working as the table grows to a thousand, ten thousand, a hundred thousand — and then, on a table with a few million orders, the request starts timing out, or the JVM heap fills up before the list even finishes loading.

The rows themselves were never the point. The report needed one Double. Everything Hibernate did to turn each database row into a managed Order entity — allocate the object, register it in the persistence context, track it for dirty checking — was work spent producing something the report immediately discarded. The fix is to ask the database for the number directly.

The mental model: an aggregate query always returns exactly one row

A normal HQL query returns as many rows as matched the WHERE clause — zero, one, or a thousand. An aggregate query is different: count, sum, avg, min, and max are defined over the entire matching set at once, so the query always produces exactly one output row, no matter how many rows fed into it. That includes zero matching rows — the diagram below is the whole idea in one picture.

The top row is the happy path: three matching products come in, count and avg come out as one row with real numbers. The bottom row is the case almost every “aggregate functions” tutorial skips — zero matching rows still produce one output row, and that row is not empty, it is 0 for count and null for everything else. That asymmetry is where the first real bug in this post comes from, verified further down with an actual test run.

The trap: an empty result set gives you 0, null, and a normal return — never an exception

Here is the query from the mental-model diagram, run for a category that does not exist. It is tempting to assume that a query matching nothing either throws or returns an empty list — neither happens for an aggregate:

Long count = em.createQuery("select count(p) from Product p where p.category = :c", Long.class)
        .setParameter("c", "no-such-category-zzz").getSingleResult();
Double sum = em.createQuery("select sum(p.price) from Product p where p.category = :c", Double.class)
        .setParameter("c", "no-such-category-zzz").getSingleResult();

AggregateFunctionsTest.java runs both of those, plus the equivalent for avg, and prints exactly what came back:

RESULT[aggregate-empty-result-set]: over 0 matching rows -- count(p)=0 (never null) | sum(p.price)=null | avg(p.price)=null -- getSingleResult() returned normally for all three, no NoResultException, because SQL's aggregate functions over zero rows still produce exactly one result row.

Full transcript: 21-empty-result-set.txt.

The bug this produces in real code. Double sum = query.getSingleResult(); compiles fine, and if the JPA provider ever returned a primitive double here it would throw a NullPointerException on autoboxing the moment the filter matches nothing. Because sum and avg return null rather than 0 on an empty set, any code that unboxes the result without a null check is one quiet product-catalog gap away from a 500 error. count is the one exception — it is defined to return 0, never null, so it is safe to unbox directly.

For the query-writing side of this — how WHERE versus HAVING changes what gets aggregated in the first place — see Hibernate Query Language (HQL) in Hibernate 7. The full write-up of this trap, plus why getSingleResult() never throws NoResultException for an aggregate, is in companion repo chapter 21.

Turning a grouped aggregate into a typed record instead of an Object[] array

Grouping becomes useful the moment you need more than one number back — a count and an average, per category, in one query. The classic way to get that back in Java is a List<Object[]>, where row[0] is the category and row[1] is a number you have to remember the type of. Hibernate 7 accepts a plain Java record’s canonical constructor as a select new target, so the projection is type-safe from the query itself:

public record CategorySummary(String category, long productCount, double averagePrice) {}

CategorySummary.java — no annotation, no mapper, just a record whose constructor parameter types and order match the query’s select new list:

List<CategorySummary> summaries = em.createQuery(
        "select new com.ankurm.hibernatedemo.aggregate.CategorySummary("
                + "p.category, count(p), avg(p.price)) "
                + "from Product p where p.category in ('Keyboards', 'Monitors') "
                + "group by p.category having count(p) > 1 "
                + "order by p.category",
        CategorySummary.class).getResultList();

Full query and test method: AggregateFunctionsTest.java.

RESULT[aggregate-groupby-having-record]: HAVING count(p) > 1 kept only categories with more than one product -- Keyboards(count=3, avg=99.0) -- Monitors (1 product) was correctly excluded by HAVING, not just by GROUP BY.

Full test and transcript: AggregateFunctionsTest.java, 21-groupby-having-record.txt.

WHERE filters individual rows before grouping happens; HAVING filters the groups after grouping has already collapsed them — the diagram’s whole point is that a one-product category survives GROUP BY just fine and only disappears at the HAVING step. Records work the same way for a non-aggregate select new projection too; see the record-projection section of the HQL post for that case.

The same query without a string: CriteriaBuilder.avg()

Anywhere the grouping condition is built at runtime — an admin search panel with optional filters, say — concatenating HQL strings is the wrong tool. The Criteria API’s aggregate methods build the identical SQL programmatically:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Object[]> cq = cb.createQuery(Object[].class);
Root<Product> root = cq.from(Product.class);
cq.multiselect(root.get("category"), cb.avg(root.get("price")))
        .where(cb.equal(root.get("category"), "Cables"))
        .groupBy(root.get("category"));

TypedQuery<Object[]> query = em.createQuery(cq);
Object[] row = query.getSingleResult();

AggregateFunctionsTest.java logs both the generated SQL and the result:

/* <criteria> */ select p1_0.category c0,avg(p1_0.price) c1 from product p1_0 where p1_0.category=? group by c0
RESULT[aggregate-criteria-avg]: Criteria API cb.avg(root.get("price")) for category='Cables' -- average=11.0 -- same numeric result as the equivalent HQL avg(p.price), just built without a string query.

Full transcript: 21-criteria-avg.txt.

cb.avg(), cb.sum(), cb.count(), cb.min(), and cb.max() all exist on CriteriaBuilder with the same null/zero rules as their HQL equivalents above — nothing about going through the Criteria API changes what an empty group returns. For the rest of the Criteria API surface — predicates, joins, dynamic WHERE construction — see Hibernate 7 Criteria Queries.

Window functions: real, but not new in Hibernate 7

HQL’s OVER clause — row_number(), rank(), lead(), lag() — runs a calculation across a set of rows without collapsing them into one, the way GROUP BY does. It genuinely works in Hibernate 7. What does not check out is the claim, repeated in more than one Hibernate 7 write-up (including an earlier version of this post), that window-function support is a Hibernate 7 feature. Disassembling hibernate-core-7.4.5.Final.jar with javap shows a windowFunctions() registration method on CommonFunctionFactory that has been present since Hibernate 6.2, where HQL’s window-function support actually landed. Hibernate 7 continues to support it; it did not introduce it.

List<Object[]> ranked = em.createQuery(
        "select p.name, p.price, "
                + "row_number() over (partition by p.category order by p.price desc) "
                + "from Product p where p.category = 'Mice' order by p.price desc",
        Object[].class).getResultList();

AggregateFunctionsTest.java — generated SQL and result:

/* select p.name, p.price, row_number() over (partition by p.category order by p.price desc) from Product p where p.category = 'Mice' order by p.price desc */ select p1_0.name,p1_0.price,row_number() over(partition by p1_0.category order by p1_0.price desc) from product p1_0 where p1_0.category='Mice' order by p1_0.price desc
RESULT[aggregate-window-row-number]: row_number() over (partition by category order by price desc) for the Mice category -- Wireless B=rank1 Wireless A=rank2 Wired C=rank3 -- HQL's window-function support (the OVER clause), present since Hibernate 6.2 and still current in 7.4.5.Final, not a Hibernate-7-only feature.

Full transcript: 21-window-row-number.txt. Note the HQL passes straight through to a real SQL window function — partition by and order by inside the OVER clause both survive translation unchanged, which is why a ranking query like this one row-numbers within each category independently rather than across the whole table.

• Aggregate functions collapse a set of rows into one; window functions annotate each row with a value computed over a set of rows around it — the row count going in equals the row count coming out. That distinction is the entire reason both exist.
• Version claims are worth checking against the jar, not the prose describing it — this is the second time in this repository a “new in version X” claim about Hibernate turned out to predate that version; see the interceptor chapter’s EmptyInterceptor correction for the first.

What the defaults do not do: a collection’s .size() is not an aggregate query

None of the queries above touch a @OneToMany collection directly, and that omission is deliberate: calling customer.getOrders().size() on a lazy collection does not run count(*) in the database — it loads every Order row for that customer into memory and counts the resulting Java list, which is exactly the memory-first trap this post opened with, just hidden behind a getter that looks free. The fix is the same one used everywhere above: push the count into the query itself, with a join and a GROUP BY, e.g. select c, count(o) from Customer c left join c.orders o group by c, which executes as a single aggregate query and never materializes the collection at all.

Should you always push aggregation into the database? Yes, with one honest exception: if the “aggregation” is really business logic too irregular for SQL — a weighted score with rules that change per customer tier, say — fetch a bounded result set with a real WHERE clause first, then compute in Java. What you should never do is fetch an unbounded set to compute something SQL already knows how to do in one pass over an index.

• getSingleResult() is only safe on an aggregate query with no GROUP BY — a grouped aggregate can legitimately return many rows, and calling getSingleResult() on more than one throws NonUniqueResultException, an entirely different failure from the null/zero case above.
• For a pure read-and-aggregate workload with no need for the first-level cache, a StatelessSession skips persistence-context bookkeeping the aggregate result never uses — covered alongside batch inserts in companion repo chapter 21.
• Sorting a grouped aggregate result (order by avg(p.price) desc) works the same as ordering any other query — covered in the sorting post.
• Paginating a large grouped result with setFirstResult/setMaxResults works normally too, with the same in-memory-fallback caveat that applies to any JOIN FETCH query — see the pagination post.

Further reading

• hibernate-demo companion repository — chapter 21 and all four tests behind this post
• Hibernate Query Language (HQL) in Hibernate 7
• Hibernate 7 Criteria Queries
• Sorting in Hibernate 7
• Hibernate 7 Pagination
• Official Hibernate ORM 7.4 User Guide — Aggregate Functions