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 |
|---|---|
3 |
|
0 (low importance) |
|
207 |
|
1 (normal importance) |
|
27 |
|
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