birt barcode tool Aggregate Functions AVG in Java

Print ECC200 in Java Aggregate Functions AVG

Depends on the type of the persistence field Depends on the type of the persistence field May return either Long or Double

If we want to find the MAX value for the i.itemPrice field among all Items, use the following query:

If you want to find out how many Category entities exist in the system, use COUNT like this:

You ve just seen some simple examples of aggregate functions. In the next section you ll learn how to aggregate results based on a path expression. Grouping with GROUP BY and HAVING In an enterprise business application, you may need to group data by some persistence field. Assuming that there is a one-many relationship between User and Category, this query will generate a report that lists the number of Category entities created by each c.user:

As you can see, we have grouped by an associated entity. You can group by a single-value path expression that is either a persistence or an association field. Only aggregate functions are allowed when you perform aggregation using GROUP BY. You can also filter the results of an aggregated query with a HAVING clause. Suppose you want to retrieve only the Users who have created more than five Category entities. Simply modify the previous query as follows:

SELECT c.user, COUNT(c.categoryId) FROM Category c WHERE c.createDate is BETWEEN :date1 and :date2 GROUP BY c.user HAVING COUNT(c.categoryId) > 5

A WHERE clause in a query containing both the GROUP BY and HAVING clauses results in multistage processing. First, the WHERE clause is applied to filter the results. Then, the results are aggregated based on the GROUP BY clause. Finally, the HAVING clause is applied to filter the aggregated result.

You can control the order of the values and objects retrieved by a query by using the ORDER BY clause:

ORDER BY path_expression1 [ASC | DESC], ... path_expressionN [ASC | DESC]

Here is an example JPQL query with an ORDER BY clause. In this case we want to retrieve all Category entities and sort them alphabetically by c.categoryName.

By specifying ASC, we ve indicated that we want the result set to be ordered in ascending order by c.categoryName. Specifying ASC is optional; if you leave it off, then the persistence provider will assume you want ascending order by default. If you want results sorted in descending order, then specify DESC for the path expression. You can use compound ordering to further customize the sorting of the query results by using

Keep in mind that if you use single-value path expressions instead of an identifier variable, the SELECT clause must contain the path expression that is used in the ORDER BY clause. The previous example used c.categoryName and c.createDate in the ORDER BY clause. Therefore, c.categoryName and c.createDate must also be used in the SELECT clause unless you use the identifier variable in the SELECT statement. This next JPQL snippet is invalid because the ORDER BY clause contains c.createDate but the SELECT clause does not:

In a JPQL query that contains both ORDER BY and WHERE clauses, the result is filtered based on the WHERE clause first, and then the filtered result is ordered using the ORDER BY clause.

A subquery is a query inside a query. You use a subquery in either a WHERE or HAVING clause to filter the result set. Unlike SQL subqueries, EJB 3 subqueries are not supported in the FROM clause. If you have a subquery in a JPQL query, the subquery

will be evaluated first, and then the main query is retrieved based on the result of the subquery. Here is the syntax for the subquery:

[NOT] IN / [NOT] EXISTS / ALL / ANY / SOME (subquery)

From the syntax of the language, it is clear that you can use IN, EXISTS, ALL, ANY, or SOME with a subquery. Let s look at some examples of subqueries in more detail. Using IN with a subquery We ve already discussed using the IN operator where a single-value path expression is evaluated against a list of values. You can use a subquery to produce a list of results:

SELECT i FROM Item i WHERE i.user IN (SELECT c.user FROM Category c WHERE c.categoryName LIKE :name)

In this query, first the subquery (in parentheses) is executed to retrieve a list of users, and then the i.item path expression is evaluated against the list. EXISTS

EXISTS (or NOT EXISTS) tests whether the subquery contains any result set. It returns true if the subquery contains at least one result and false otherwise. Here is an example illustrating the EXISTS clause:

SELECT i FROM Item i WHERE EXISTS (SELECT c FROM Category c WHERE c.user = i.user)

If you look carefully at the result of this subquery, you ll notice that it is the same as the query example we used in the previous section with the IN operator. An EXISTS clause is generally preferred over IN, particularly when the underlying tables contain a large number of records. This is because databases typically perform better when using EXISTS. Again, this is due to the work of the query processor translating JPQL queries into SQL by the persistence provider. ANY, ALL, and SOME Using the ANY, ALL, and SOME operators is similar to using the IN operator. You can use these operators with any numeric comparison operators, such as =, >, >=, <, <= and <>.