Getting Item Counts with GROUP BY

Getting Item Counts with GROUP BY

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

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)

VBScript

' 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

Send us your feedback about the Microsoft Exchange Server 2003 SDK.

Build: June 2007 (2007.618.1)

© 2003-2006 Microsoft Corporation. All rights reserved. Terms of use.