-
Notifications
You must be signed in to change notification settings - Fork 10
Aggregate Expressions
Konstantin Triger edited this page Aug 20, 2019
·
4 revisions
Some aggregate functions, like COUNT()
, SUM()
or AVG()
are order independent. But some are dependent, like: FIRST()
, LAST()
, STRING_AGG()
etc. There are 2 different syntax types in SQL to provide ordering to the aggregate, within an aggregate:
SELECT string_agg(a, ',' ORDER BY a) FROM table;
OR using WITHIN GROUP
construct:
SELECT LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date) "Employees";
FluentJPA supports both variants and you should refer to your vendor docs to choose the right one. The same in FluentJPA:
// first option with ALL or DISTINCT
SELECT(STRING_AGG(ALL(x.getA(), ",").ORDER(BY(x.getA()))));
SELECT(STRING_AGG(DISTINCT(x.getA(), ",").ORDER(BY(x.getA()))));
// second option
SELECT(alias(aggregateBy(LISTAGG(emp.getLastName(), "; "))
.WITHIN_GROUP(ORDER(BY(em.getHireDate()))), "Employees"));
Getting Started
- Introduction
- Setup
- Data Types
- Entities & Tuples
- Sub Queries
- JPA Integration
- Java Language Support
- Directives
- Library
- Returning Results
- JPA Repositories
Examples
Basic SQL DML Statements
Advanced SQL DML Statements
- Common Table Expressions (WITH Clause)
- Window Functions (OVER Clause)
- Aggregate Expressions
- MERGE
- Temporal Tables
Advanced Topics