Aggregate Functions

Enterprise Definition Language › Aggregate Functions

Aggregate Functions

EDL supports the following aggregate functions:

sum()
average()
count()
max()
min()
All aggregate functions except count () work on numerical input; you can use count () with other data types, such as string and enum:

Examples	Statements
Volumes Containing More Than 100,000 Files	To select all the volumes that contain more than 100,000 files, enter the following: VOLUME_NAME WHERE COUNT(FILE_FULL_NAME) > 100000
TSM Servers Where Total Objects Failed to Backup Greater Than Average	To select all the TSM servers on which the total number of objects that failed to back up is greater than the average, enter the following: TSM_SERVER_NAME WHERE TSM_SERVER_OBJECTS_FAILED > AVERAGE(TSM_SERVER_OBJECTS_FAILED)
TSM Nodes with More Than 100 Backup Sessions	To select all the TSM nodes that had more than 100 backup sessions, enter the following: TSM_NODE WHERE COUNT(TSM_SESSION) > 100

Examples

Statements

Volumes Containing More Than 100,000 Files

To select all the volumes that contain more than 100,000 files, enter the following:

VOLUME_NAME WHERE COUNT(FILE_FULL_NAME) > 100000

TSM Servers Where Total Objects Failed to Backup Greater Than Average

To select all the TSM servers on which the total number of objects that failed to back up is greater than the average, enter the following:

TSM_SERVER_NAME WHERE TSM_SERVER_OBJECTS_FAILED > AVERAGE(TSM_SERVER_OBJECTS_FAILED)

TSM Nodes with More Than 100 Backup Sessions

To select all the TSM nodes that had more than 100 backup sessions, enter the following:

TSM_NODE WHERE COUNT(TSM_SESSION) > 100

You can use aggregate functions with the keyword BY:

Examples	Statements
Query Table All Files Belonging to Owners Using More Than 100 MB of Space	To define a query result table that lists all files belonging to owners who use more than 100 MB of space, enter the following: FILE_FULL_NAME, OWNER_NAME WHERE SUM(FILE_SIZE) BY OWNER > 100 MB
Query Table TSM Nodes Registered on TSM Servers That Transferred More Than 100 GB of Backup Files	To define a query result table that lists all TSM nodes registered on TSM servers that transferred more than 100 GB of backup files, enter the following: TSM_SERVER_NAME, TSM_NODE_NAME WHERE SUM(TSM_NODE_BYTES_XFERRED) BY TSM_SERVER > 100 GB

Examples

Statements

Query Table All Files Belonging to Owners Using More Than 100 MB of Space

To define a query result table that lists all files belonging to owners who use more than 100 MB of space, enter the following:

FILE_FULL_NAME, OWNER_NAME WHERE SUM(FILE_SIZE) BY OWNER > 100 MB

Query Table TSM Nodes Registered on TSM Servers That Transferred More Than 100 GB of Backup Files

To define a query result table that lists all TSM nodes registered on TSM servers that transferred more than 100 GB of backup files, enter the following:

TSM_SERVER_NAME, TSM_NODE_NAME WHERE SUM(TSM_NODE_BYTES_XFERRED) BY TSM_SERVER > 100 GB

You can use aggregate functions with the keyword AS. When you apply an aggregate function to an object attribute in query definition, you should see that you assign a custom name to the attribute column to make the result more readable. If you do not do this, the default column name will contain both attribute and function names, which may be too long.