| COLUMN      | TYPE          |
| ----------- | ------------- |
|  ID         | VARCHAR2(200) |
|  NAME       | VARCHAR2(200) |
|  AGE        | NUMBER(5)     |
|  MAIN_CITY  | VARCHAR2(200) |
|  GENDER     | VARCHAR2(50)  |
|  SCORE      | FLOAT(5)      |

SELECT /*+ parallel(2)*/ *
FROM (WITH filtered_values(age, gender, main_city) AS
(
SELECT /*+ MATERIALIZE */ age, gender, main_city 
FROM Students
-- GET CONDITIONS FROM USER AND FILTER THE QUERY USING WHERE
)
SELECT * 
FROM (
SELECT age AS value, COUNT(*) AS row_count, 'age' AS column_name
FROM filtered_values
GROUP BY age
ORDER BY row_count)
WHERE rownum <= 5
UNION ALL (
SELECT * 
FROM (
SELECT gender AS value, COUNT(*) AS row_count, 'gender' AS column_name
FROM filtered_values
GROUP BY gender
ORDER BY row_count)
WHERE rownum <= 5)
UNION ALL (
SELECT * 
FROM (
SELECT main_city AS value, COUNT(*) AS row_count, 'main_city' AS column_name
FROM filtered_values
GROUP BY main_city
ORDER BY row_count)
WHERE rownum <= 5)
)

@Data
@Entity
@NoArgsConstructor
@AllArgsConstructor
@Table(schema = "PROD", name = "Students")
public class Student implements Serializable {
@Id
@Column(name = "ID")
private String id;
@Column(name = "NAME")
private String name;
@Column(name = "AGE")
private int age;
@Column(name = "MAIN_CITY")
private String mainCity;
@Column(name = "SCORE")
private float score; 
}

@Data
@Entity
@NoArgsConstructor
@AllArgsConstructor
@Table(schema = "PROD", name = "NOT_REAL_TABLE")
public class AggregationValues {
private int age;
private String gender;
private String mainCity;
}

@Data
@NoArgsConstructor
@AllArgsConstructor
public class AggregationResultRow {
private String value;
private long rowCount;
private String columnName;
}

private final QStudent table = QStudent.student;
private final QAggregationValues aggregationValues = QAggregationValues.aggregationValues;
public List<AggregationResultRow> getAggergations(AggregationRequest aggregationRequest) {
SubQueryExpression<AggregationValues> filteredValues = new BlazeJPAQuery<AggregationValues>(entityManager, criteriaBuilderFactory)
.select(Projections.constructor(AggregationValues.class, 
this.table.age,
this.table.gender,
this.table.mainCity))
.from(this.table)
.where(this.getConditions(aggregationRequest));
BlazeJPAQuery<AggregationResultRow> ageSelect = new BlazeJPAQuery<AggregationResultRow>(entityManager, criteriaBuilderFactory)
.with(this.aggregationValues, filteredValues)
.select(Projections.constructor(AggregationResultRow.class, this.aggregationValues.age.as("value"),
this.aggregationValues.count().as("row_count"),
Expressions.asString("age").as("column_name")))
.from(this.aggregationValues)
.groupBy(this.aggregationValues.age);
BlazeJPAQuery<AggregationResultRow> genderSelect = new BlazeJPAQuery<AggregationResultRow>(entityManager, criteriaBuilderFactory)
.with(this.aggregationValues, filteredValues)
.select(Projections.constructor(AggregationResultRow.class, this.aggregationValues.gender.as("value"),
this.aggregationValues.count().as("row_count"),
Expressions.asString("gender").as("column_name")))
.from(this.aggregationValues)
.groupBy(this.aggregationValues.gender);
BlazeJPAQuery<AggregationResultRow> mainCitySelect = new BlazeJPAQuery<AggregationResultRow>(entityManager, criteriaBuilderFactory)
.with(this.aggregationValues, filteredValues)
.select(Projections.constructor(AggregationResultRow.class, this.aggregationValues.mainCity.as("value"),
this.aggregationValues.count().as("row_count"),
Expressions.asString("main_city").as("column_name")))
.from(this.aggregationValues)
.groupBy(this.aggregationValues.mainCity);
return ageSelect.unionAll(genderSelect, mainCitySelect).fetch();
}

@Autowired
private JdbcTemplate jdbcTemplate;

public List<AggregationResultRow> getAggregations(AggregationRequest aggregationRequest) throw Exception {
Connection connection = this.jdbcTemplate.getDataSource().getConnection();
OracleQuery<AggregationResultRow> query = new OracleQuery<AggregationResultRow>(connection)
.with(this.aggregationValues, new OracleQuery<AggregationValues>(connection)
.select(Projections.constructor(AggregationValues.class, this.table.age.as(this.aggregationValues.age), this.table.gender.as(this.aggregationValues.gender),
this.table.mainCity.as(this.aggregationValues.mainCity)))
.from(this.table)
where(this.getConditions(aggregationRequest)));
query.unionAll(new OracleQuery<AggregationResultRow>(connection)
.select(Projections.constructor(AggregationResultRow.class, this.aggregationValues.age.as(“value”),
this.aggregationValues.age.count().as(“row_count”),
Expressions.asString(“age”).as(“column_name”)))
.from(this.aggregationValues)
.groupBy(this.aggregationValues.age),
new OracleQuery<AggregationResultRow>(connection)
.select(Projections.constructor(AggregationResultRow.class, this.aggregationValues.gender.as(“value”),
this.aggregationValues.gender.count().as(“row_count”),
Expressions.asString(“gender”).as(“column_name”)))
.from(this.aggregationValues)
.groupBy(this.aggregationValues.gender),
new OracleQuery<AggregationResultRow>(connection)
.select(Projections.constructor(AggregationResultRow.class, this.aggregationValues.mainCity.as(“value”),
this.aggregationValues.mainCity.count().as(“row_count”),
Expressions.asString(“mainCity”).as(“column_name”)))
.from(this.aggregationValues)
.groupBy(this.aggregationValues.mainCity));
return query.fetch();
}