Aggregate functions

Using aggregate functions

DbAssist supports the aggregate SQL functions by implementing the following methods inside AbstractRepository.

• count()
• avg()
• sum()
• sumAsLong()
• sumAsDouble()
• min()
• max()
• least()
• greatest()

The rest of the article shows how to use the selected methods. A few examples show also how to add conditions on the query.

count

The count method returns Long object.

ConditionsBuilder c = new ConditionsBuilder();
HierarchyCondition hc = c.equal("name", "Mont");
c.apply(hc);
Long count = uRepo.count(c);

Result:

SELECT COUNT(id) FROM users WHERE name = 'Mont'

sum

The sum method returns whatever type is returned as a result of SQL query. For example Integer for INT database type, and Double for DOUBLE type.

ConditionsBuilder c = new ConditionsBuilder();
HierarchyCondition hc = c.lessThan("salary", 10000.0);
c.apply(hc);
Long count = uRepo.sum(c, "id");

Result:

SELECT SUM(id) FROM users WHERE salary < 10000.0

avg

The avg method returns Double as a result of SQL query.

Double avg = uRepo.avg(new ConditionsBuilder(), "salary");

Result:

SELECT AVG(salary) FROM users