Overview of Aggregate Functions

Queries Combining Aggregates and Fields

Caché SQL allows you to specify an aggregate function with other SELECT items in a query. An aggregate such as COUNT(*) does not need to be in a separate query.

SELECT TOP 5 COUNT(*),Name,AVG(Age)
FROM Sample.Person
ORDER BY Name

When you specify an aggregate function and specify one or more field select items in the select list, Caché SQL returns as many rows as required for the field item:

SELECT DISTINCT Age,AVG(Age),LIST(Age)
FROM Sample.Person
WHERE Age > 75

Aggregate-only Queries

When you specify one or more aggregate functions and specify no field select items in the select list, Caché SQL returns one row. A TOP clause is ignored, unless it is TOP 0 (return no rows):

SELECT TOP 7 AVG(Age),LIST(Name)
FROM Sample.Person
WHERE Age > 75

If the query returns no table data, an aggregates-only query still returns one row (%ROWCOUNT=1). The aggregate host variables are defined and set to the empty string, except for COUNT which is defined and set to 0. This is shown in the following Embedded SQL example:

   KILL a,b
   &sql(SELECT AVG(Age),LIST(Name) INTO :a,:b FROM Sample.Person 
        WHERE Age > 999)
   WRITE %ROWCOUNT,!
   WRITE "#",a,"#",b,"#"  

However, if the select-list includes one or more field select items, or if the aggregate-only query contains a GROUP BY clause or a TOP 0 clause, the query returns 0 rows (%ROWCOUNT=0) and the aggregate INTO host variables are undefined.

Column Names and Aliases

By default, the column name assigned to the results of an aggregate function is Aggregate_n, where the n number suffix is the column order number, as specified in the SELECT list. Thus, the following example creates column names Aggregate_2 and Aggregate_5:

SELECT TOP 5 Home_State,COUNT(*),Name,Age,AVG(Age)
FROM Sample.Person
ORDER BY Name

To specify another column name (a column alias), use the AS keyword:

SELECT COUNT(*) AS PersonCount
FROM Sample.Person,Sample.Employee

You can use a column alias to specify an aggregate field in an ORDER BY clause. The following example lists people in the order that their ages diverge from the average age:

SELECT Name,Age,
    AVG(Age) AS AvgAge,
    ABS(Age - AVG(Age)) AS RelAge
FROM Sample.Person
ORDER BY RelAge

For further details on column aliases, refer to the SELECT statement.

With ORDER BY

The LIST, %DLIST, XMLAGG, and JSON_ARRAYAGG functions combine the values of a table column from multiple rows into a single aggregate value. Because an ORDER BY clause is applied to the query result set after all aggregate fields are evaluated, ORDER BY cannot directly affect the sequence of values within these aggregates. Under certain circumstances, the results of these aggregates may appear in sequential order, but this ordering should not be relied upon. The values listed within a given aggregate result value cannot be explicitly ordered.

With DISTINCT and GROUP BY

A SELECT DISTINCT with a select-item aggregate function and a GROUP BY clause returns the same results as if the DISTINCT keyword were not present. To achieve the desired results, put the aggregate function in a subquery.

For example, you wish to return the number of distinct counts of persons in states (there are states with 4 people, there are states with 6 people, etc.). You would expect to achieve this result as follows:

SELECT DISTINCT COUNT(*) AS PersonCounts
FROM Sample.Person
GROUP BY Home_State

Instead, you get a person count for each state, the same as if the DISTINCT keyword were not present:

SELECT COUNT(*) AS PersonCounts
FROM Sample.Person
GROUP BY Home_State

To achieve your intended result, you need to use a subquery, as follows:

SELECT DISTINCT * 
FROM (SELECT COUNT(*) AS PersonCounts FROM Sample.Person
      GROUP BY Home_State)

Row Counts

In a query that contains one or more aggregate functions the %ROWCOUNT value depends on the query:

• If the select-list does not contain any references to fields in the FROM clause table(s), other than fields supplied to aggregate functions, the query returns %ROWCOUNT 1 when the query selects no rows. COUNT returns 0, other aggregate functions return NULL:

    ZNSPACE "SAMPLES"
    SET q1="SELECT COUNT(*) AS NumRows,COUNT(FavoriteColors) AS NumColors,"_
           "AVG(Age) AS AvgAge,MAX(Age) AS MaxAge FROM Sample.Person"
    SET q2="SELECT COUNT(*) AS NumRows,COUNT(FavoriteColors) AS NumColors,"_
           "AVG(Age) AS AvgAge,MAX(Age) AS MaxAge FROM Sample.Person WHERE Name='ZZZ'"
    SET tStatement = ##class(%SQL.Statement).%New()
  QueryReturnsData
    SET qStatus = tStatement.%Prepare(q1)
      IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
    SET rset = tStatement.%Execute()
    DO rset.%Display()
    WRITE !,"Rowcount with data:",rset.%ROWCOUNT,!!
  QueryReturnsNoData
    SET qStatus = tStatement.%Prepare(q2)
      IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
    SET rset = tStatement.%Execute()
    DO rset.%Display()
    WRITE !,"Rowcount without data:",rset.%ROWCOUNT  

  

  The select-item list can include other items, so long as they do not reference fields in the FROM clause table(s):

    ZNSPACE "SAMPLES"
    SET q1="SELECT COUNT(*) AS NumRows,COUNT(Name) AS NumNames,"_
           "(SELECT Name FROM Sample.Company) AS SubQ,$LENGTH('this string'),%CLASSNAME "_
           "FROM Sample.Person WHERE Name='ZZZ'"
    SET tStatement = ##class(%SQL.Statement).%New()
    SET qStatus = tStatement.%Prepare(q1)
      IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
    SET rset = tStatement.%Execute()
    DO rset.%Display()
    WRITE !,"Rowcount:",rset.%ROWCOUNT
  

  

  However, if this type of query selects no rows and has a GROUP BY clause, it returns %ROWCOUNT 0.

• A query containing TOP 0 always returns %ROWCOUNT 0. Aggregate functions are not evaluated:

    ZNSPACE "SAMPLES"
    SET q1="SELECT TOP 0 COUNT(*) AS NumRows,COUNT(Name) AS NumNames,"_
           "AVG(Age) AS AvgAge FROM Sample.Person"
    SET tStatement = ##class(%SQL.Statement).%New()
    SET qStatus = tStatement.%Prepare(q1)
      IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
    SET rset = tStatement.%Execute()
    DO rset.%Display()
    WRITE !,"Rowcount:",rset.%ROWCOUNT
  

  

• A query containing aggregate functions and fields returns the %ROWCOUNT of rows returned. If the result set contains no rows, the query returns %ROWCOUNT 0, COUNT returns NULL:

    ZNSPACE "SAMPLES"
    SET q1="SELECT TOP 4 COUNT(*) AS NumRows,COUNT(Name) AS NumNames,"_
           "Name,AVG(Age) AS AvgAge FROM Sample.Person"
    SET q2="SELECT TOP 4 COUNT(* )AS NumRows,COUNT(Name) AS NumNames,"_
           "Name,AVG(Age) AS AvgAge FROM Sample.Person WHERE Name='ZZZ'"
    SET tStatement = ##class(%SQL.Statement).%New()
  QueryReturnsData
    SET qStatus = tStatement.%Prepare(q1)
      IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
    SET rset = tStatement.%Execute()
    DO rset.%Display()
    WRITE !,"Rowcount with data:",rset.%ROWCOUNT,!!
  QueryReturnsNoData
    SET qStatus = tStatement.%Prepare(q2)
      IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
    SET rset = tStatement.%Execute()
    DO rset.%Display()
    WRITE !,"Rowcount without data:",rset.%ROWCOUNT