How to use CriteriaQuery SUM of custom operation on some cells?

Question

Consider you have table T, with fields A and B.

With regular SQL, I could do this:

SELECT SUM(A * (100.0 - B) / 100.0) AS D FROM T;

And I would get exactly what I expect.

However, I'm not sure how to do it with CriteriaQuery.

I know how to do sum over 1 field, but not how to do sum over some math expression over multiple fields in a row.

Wait

Answer 1

The CriteriaBuilder interface provides the following arithmetic functions:

• addition: sum(a, b)
• substraction: diff(a, b)
• multiplication: prod(a, b)
• division: quot(a, b)

where a b parameters can be an expression and/or literal.

As for the query, here is an exampe written in a human readable form:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Number> q = cb.createQuery(Number.class);
Root<T> t = q.from(T.class);

// build SUM(A * (100.0 - B) / 100.0) expression
Expression<Double> diff = cb.diff(100.0, t.<Double>get("B"));
Expression<Double> prod = cb.prod(t.<Double>get("A"), diff);
Expression<Number> quot = cb.quot(prod, 100.0);
Expression<Number> sum = cb.sum(quot);
q.select(sum.alias("D"));

System.out.println(em.createQuery(q).getSingleResult());

You can also build the query as an one-liner:

q.select(cb.sum(cb.quot(cb.prod(t.<Double>get("A"), cb.diff(100.0, t.<Double>get("B"))), 100.0)).alias("D"));

I hope it clarifies your doubts.

Agustinaah

i am getting error as "ERROR: ERROR: column "cmshcpcsco8_.nonfacility_pe_rvu" must appear in the GROUP BY clause or be used in an aggregate function". any idea ?? – Merriemerrielle

@Agustinaah quot method returns quotient. How to get division of two expressions? – Throughput

I wonder why divide or multiply are not chosen and quot, prod are used instead... It would have saved me quite some time while looking for this – Kenley