Aggregates, Grouping, and Sorting

You can use aggregate functions let you summarize the data retrieved in a query.

The aggregate functions are: sum, avg, count, min, max, count_big, count(*), and count_big(*). .

If your SAP ASE server is not case sensitive, see group by and having clauses and compute clause in the Reference Manual: Commands for examples on how case sensitivity affects the data returned by these clauses.

To find out the number of books sold in the titles table of the pubs2 database, enter:
select sum(total_sales)
from titles
------------- 
       97746

There is no column heading for the aggregate column in the example.

An aggregate function takes as an argument the column name on which values it operates. You can apply aggregate functions to all the rows in a table, to a subset of the table specified by a where clause, or to one or more groups of rows in the table. From each set of rows to which an aggregate function is applied, SAP ASE generates a single value.

Here is the syntax of the aggregate function: 

aggregate_function ( [all | distinct] expression)

expression is usually a column name. However, it can also be a constant, a function, or any combination of column names, constants, and functions connected by arithmetic or bitwise operators. You can also use a case expression or subquery in an expression.

For example, with this statement, you can calculate the average price of all books if prices were doubled: 

select avg(price * 2) 
from titles 
------------- 
        29.53 
 
(1 row affected)

Use the optional keyword distinct with sum, avg, count, min, and max to eliminate duplicate values before the aggregate function is applied. all, which performs the operation on all rows, is the default.

This is the syntax of the aggregate functions and the results they produce:

Aggregate Function

Result

sum([all | distinct] expression)

Total of the (distinct) values in the expression

avg([all | distinct] expression)

Average of the (distinct) values in the expression

count([all | distinct] expression)

Number of (distinct) non-null values in the expression returned as an integer

count_big ([all | distinct] expression)

Number of (distinct) non-null values in the expression returned as a bigint

count(*)

Number of selected rows as an integer

count_big(*)

Number of selected rows as a bigint

max(expression)

Highest value in the expression

min(expression)

Lowest value in the expression

You can use the aggregate functions in a select list, as shown in the previous example, or in the having clause.

You cannot use aggregate functions in a where clause, but most select statements with an aggregate function in the select list include a where clause that restricts the rows to which the aggregate is applied. In the examples given earlier in this section, each aggregate function produced a single summary value for the entire table.

If a select statement includes a where clause, but not a group by clause, an aggregate function produces a single value for the subset of rows, called a scalar aggregate. However, a select statement can also include a column in its select list (a Transact-SQL extension), that repeats the single value for each row in the result table.

This query returns the average advance and the sum of sales for only business books, preceded by a column named “advance and sales:”

select "advance and sales", avg(advance), sum(total_sales)
from titles 
where type = "business"
-----------------  ----------------- ----------- 
advance and sales          6,281.25       30788  

(1 row affected)