AggregateFunction

Description

Sets or returns a SQL aggregate function for the field. When setting an aggregate function, the function you select must make sense for the field type.

For example, use DB_AGGR_COUNT to return the count of RECORDS that have some value in that field. You would typically choose dbid as the field to count, since you typically mean to count all records. The other functions return either the minimum value, maximum value, the average value, or the sum of a field. Sum and Average only work for numeric fields.

For example, "select count(dbid) from defect" returns the total number of defects in the database.

You can also specify a group-by field, and get the statistic broken down by some other field. For example, "select count(dbid), owner from defect group by owner" returns the number of defects per owner, showing you the count and the owner for each owner.

Mixing these aggregate functions with other fields in the select clause is usually not valid unless they are also mentioned in a group-by clause. You can have more than one aggregate however, such as "select count(dbid), max(severity), min(due_date), owner group by owner."

Syntax

VBScript


queryfielddef.AggregateFunction 
queryfielddef.AggregateFunction NewValue

Perl


$queryfielddef->GetAggregateFunction 
$queryfielddef->SetAggregateFunction(NewValue);

Identifier: Description
queryfielddef: A QueryFieldDef object.
NewValue: A Long that specifies the function type for the field. The value corresponds to one of the DbAggregate constants.
Return value: Returns a Long that identifies the function type for the field. The value corresponds to one of the DbAggregate constants.

Example

Perl


use CQPerlExt;

#Start a HCL Compass session

#$AdminSession= CQAdminSession::Build();

$SessionObj = CQSession::Build();



$dbsetname = "CQMS.SAMPL.HOME";

#Refresh list of accessible databases

$databases = $SessionObj->GetAccessibleDatabases("MASTR", "", $dbsetname);



#Log into a database

$SessionObj->UserLogon("admin","","SAMPL",$dbsetname);



$querydef = $SessionObj->BuildQuery("defect") ;

$querydef->BuildField("id") ;

$querydef->BuildField("owner.login_name") ;



$queryfielddefs = $querydef->GetQueryFieldDefs();

$idfield = $queryfielddefs->ItemByName("id");

$idfield->SetAggregateFunction($CQPerlExt::CQ_DB_AGGR_COUNT);



$ownerfield = $queryfielddefs->ItemByName("owner.login_name");

$ownerfield->SetIsGroupBy(1);



$resultset = $SessionObj->BuildResultSet($querydef);

$ct = $resultset->ExecuteAndCountRecords();

for ($i = 0; $i < $ct; $i++) {

   $resultset->MoveNext();

   print $resultset->GetColumnValue(1);

   print " ";

   print $resultset->GetColumnValue(2);

   print "\n";

}

CQAdminSession::Unbuild($AdminSession);

CQSession::Unbuild($SessionObj);