SQL聚合函数 COUNT

COUNT(*)

COUNT([ALL | DISTINCT [BY(col-list)]] expression [%FOREACH(col-list)] [%AFTERHAVING])

ClassMethod Count()
{
    s myquery = 3
    s myquery(1) = "SELECT COUNT(*) AS Recs,COUNT(Name) AS People,"
    s myquery(2) = "AVG(Age) AS AvgAge,MAX(Age) AS MaxAge,CURRENT_TIMESTAMP AS Now"
    s myquery(3) = " FROM Sample.Employee WHERE Name %STARTSWITH 'ZZZ'"
    s tStatement = ##class(%SQL.Statement).%New()
    s qStatus = tStatement.%Prepare(.myquery)
    if qStatus'=1 {
        w "%Prepare failed:" 
        d $System.Status.DisplayError(qStatus) 
        q
    }
    s rset = tStatement.%Execute()
    d rset.%Display()
    w !,"Rowcount:",rset.%ROWCOUNT
}

DHC-APP> d ##class(PHA.TEST.SQLCommand).Count()
Recs    People  AvgAge  MaxAge  Now
0       0                       2021-12-20 20:58:17
 
1 Rows(s) Affected
Rowcount:1

ClassMethod Count1()
{
    s myquery = 2
    s myquery(1) = "SELECT COUNT(*) AS Recs,COUNT(Name) AS People,$LENGTH(Name) AS NameLen"
    s myquery(2) = " FROM Sample.Employee WHERE Name %STARTSWITH 'ZZZ'"
    s tStatement = ##class(%SQL.Statement).%New()
    s qStatus = tStatement.%Prepare(.myquery)
    if qStatus'=1 {
        w "%Prepare failed:" 
        d $System.Status.DisplayError(qStatus) 
        q
    }
    s rset = tStatement.%Execute()
    d rset.%Display()
    w !,"Rowcount:",rset.%ROWCOUNT
}

DHC-APP> d ##class(PHA.TEST.SQLCommand).Count1()
Recs    People  NameLen
 
0 Rows(s) Affected
Rowcount:0

ClassMethod Count2()
{
    s myquery = "SELECT COUNT(*) AS Recs"
    s tStatement = ##class(%SQL.Statement).%New()
    s qStatus = tStatement.%Prepare(myquery)
    if qStatus'=1 {
        w "%Prepare failed:" 
        d $System.Status.DisplayError(qStatus) 
        q
    }
    s rset = tStatement.%Execute()
    d rset.%Display()
    w !,"Rowcount:",rset.%ROWCOUNT
}

DHC-APP> d ##class(PHA.TEST.SQLCommand).Count2()
Recs
1
 
1 Rows(s) Affected
Rowcount:1