Getting Item Counts by using GROUP BY

Topic Last Modified: 2006-06-12

You can use the GROUP BY clause to get a tally of specific items, such as the number of messages with low, normal, or high importance. The DAV:visiblecount property contains the total for each group in the returned recordset.

Note

Using the GROUP BY query produces a recordset that does not support Microsoft® ActiveX® Data Objects (ADO) bookmarks. Attempts to use methods that are only available for Recordsets that support bookmarks, such as the Recordset.Clone method, on a Recordset generated with the GROUP BY predicate, will return an error.

The property values shown in the following table would return a tally of 3 low-importance, 207 normal-importance, and 27 high-importance, messages.

Property	Value
DAV:visiblecount	3
urn:schemas:httpmail:importance	0 (low importance)
DAV:visiblecount	207
urn:schemas:httpmail:importance	1 (normal importance)
DAV:visiblecount	27
urn:schemas:httpmail:importance	2 (high importance)

Example

VBScript

Example

' Tallies the number of high, normal, and low importance messages.

On Error GoTo ErrHandler

Const adErrNoCurrentRecord = 3021

' Get computer and domain information.
Set info   = CreateObject("ADSystemInfo")
Set infoNT = CreateObject("WinNTSystemInfo")
cName = infoNT.ComputerName
dName = info.DomainDNSName

' Create the connection object.
Set Conn = CreateObject("ADODB.Connection")
Conn.Provider = "Exoledb.DataSource"

' The URL for the connection object
' is at the virtual directory root.
' You must have access to userA's mailbox
' for this sample to work.
cURL = "http://" & cName & "." & dName & "/" & "exchange/userA"

Conn.Open cURL

' The relative URL is the folder to search.
relURL = "Inbox"

Set rs = CreateObject("ADODB.Recordset")

' Construct the SQL query.
strQ = "SELECT ""DAV:visiblecount"", "
strQ = strQ & " ""urn:schemas:httpmail:importance"" "
strQ = strQ & "FROM """ & relURL & """ "
strQ = strQ & "GROUP BY ""urn:schemas:httpmail:importance"" "

Rs.Open strQ, Conn

' If the recordset is empty, return an error.
' If it was successful, display the results.
If Rs.EOF = True Then
   WScript.Echo "No items found, run another query."
Else
   Rs.MoveFirst
   Do Until Rs.EOF
      Select Case Rs("urn:schemas:httpmail:importance")
         Case 2
            strI = "High"
         Case 1
            strI = "Normal"
         Case 0
            strI = "Low"
      End Select

      WScript.Echo Rs("DAV:visiblecount") & " " & strI & " importance messages"
      Rs.MoveNext
  Loop
End If

GoTo Ending

' Implement custom error handling here.
ErrHandler:
   WScript.echo Err.Number + " " + Err.Description
   Err.Clear

Ending:
   Conn.Close
   Rs.Close

   Set Conn = Nothing
   Set Rs = Nothing