SelectAggregateValues

How do I get aggregate results, eg. "SELECT MAX()"?

Use a ReportQuery. Eg. this executes "SELECT MAX(version_number) AS max_version FROM file_version WHERE fileID = xxx".

ExpressionBuilder fileVersion = new ExpressionBuilder(); 
Expression exp = fileVersion.get("file").equal(file); // equal IDs
        
ReportQuery q = new ReportQuery(FileVersion.class, exp);
q.addMaximum("max_version", fileVersion.get("versionNumber"));
q.returnSingleValue();

Number maxVersionNum = (Number) getSession().executeQuery(q);
return maxVersionNum.longValue();

Example With Sums

forum:258937 I have the following report query:

ExpressionBuilder opp = new ExpressionBuilder();
ReportQuery query = new ReportQuery(Opportunity.class, opp);
Expression revenueForecasts = opp.anyOf("revenueForcasts");

query.addSum("totalConstruction", revenueForecasts.get("construction"));
query.addSum("totalConsultancy", revenueForecasts.get("consultancy"));
query.addSum("totalOperateAndMaintain", revenueForecasts.get("operateAndMaintain"));
query.addSum("totalOther", revenueForecasts.get("other"));
query.addAttribute("marketSegment", opp.get("marketSegment"));
query.setSelectionCriteria(opp.get("likelihoodOfSuccess").greaterThanEqual(liklihoodOfSuccess)
.and(revenueForecasts.get("year").equal(year)));

query.addOrdering(opp.get("marketSegment"));
query.addGrouping(opp.get("marketSegment"));

I'm trying to get Toplink to add all the totals for me. Either to sum the 3 fields and then do a addSum() or do 3 addSums and add these.

This is just a matter of using the ExpressionMath? class.

Firstly, change the sums to expressions:

query.addSum("totalConstruction", revenueForecasts.get("construction"));
query.addSum("totalConsultancy", revenueForecasts.get("consultancy"));
query.addSum("totalOperateAndMaintain", revenueForecasts.get("operateAndMaintain"));
query.addSum("totalOther", revenueForecasts.get("other"));

becomes:

Expression totalConstruction=revenueForecasts.get("construction").sum();
Expression totalConsultancy=revenueForecasts.get("consultancy").sum();
Expression totalOperateAndMaintain=revenueForecasts.get("operateAndMaintain").sum();
Expression totalOther=revenueForecasts.get("other").sum();

Next, we use ExpressionMath?.add to add these together:

Expression total1=ExpressionMath.add(totalConstruction,totalConsultancy);
Expression total2=ExpressionMath.add(totalOperateAndMaintain,totalOther);
Expression total=ExpressionMath.add(total1,total2);

And then we add these to the report query:

query.addAttribute("totalConstruction", totalConstruction);
query.addAttribute("totalConsultancy", totalConsultancy);
query.addAttribute("totalOperateAndMaintain", totalOperateAndMaintain);
query.addAttribute("totalOther", totalOther);
query.addAttribute("total",total);

See Also

SelectCountStar, HowTo fetch aggregate data using ReportQuery