PROPCOUNT

Returns the ratio of the COUNT aggregate function calculated on a group to the COUNT aggregate function calculated on a hierarchically higher group.

Arguments:

*

Specifies that all records should be counted to return the total number of records, including records that contain NULL values.

<field_expr_list>

Specifies that only records for which at least one of the specified field-expressions is non-NULL should be counted.

<on_field_expr_list>

List of GROUP BY field-expressions identifying the hierarchically higher group on which the denominator COUNT aggregate function is to be calculated.
This list of field-expressions must be a proper prefix of the GROUP BY field-expressions, that is, it must contain, in the same order, a subset of the field-expressions specified in the GROUP BY clause, starting with the leftmost GROUP BY field-expression.
When this list of field-expressions is not specified, the denominator COUNT aggregate function is calculated on the whole set of input records.

Return Type:

REAL

Remarks:

• When used without a GROUP BY clause, the PROPCOUNT aggregate function always returns 1.0. In fact, in this case the only hierarchically higher group available is the whole set of input records, and the ratio numerator and denominator are calculated on the same set.
• To obtain a percentage, multiply the return value of the PROPCOUNT aggregate function by 100.0, using the MUL function.
• Aggregate functions are allowed as field-expressions only in the SELECT, HAVING, and ORDER BY clauses.
• The arguments of an aggregate function can not reference other aggregate functions.
• The arguments of an aggregate function can not reference the following functions:
  • SEQUENCE
  • OUT_ROW_NUMBER

Examples:

A. PROPCOUNT(*)

The following query returns the percentage of events for each source in the System event log:

SELECT SourceName, MUL(PROPCOUNT(*), 100.0) AS Percent
FROM System
GROUP BY SourceName

A sample output of this query is:

SourceName              Percent
----------------------- ---------
EventLog                10.322979
Service Control Manager 63.004172
Ati HotKey Poller       3.430691
Application Popup       0.108175
W32Time                 14.680884
DCOM                    0.046361
NtServicePack           0.185443
Win32k                  0.324525
RemoteAccess            2.194406
GEMPCC                  0.509968
SCardSvr                0.509968
Dhcp                    0.262711
i8042prt                0.015454
Print                   0.030907
Tcpip                   0.077268
Workstation             0.015454
NETLOGON                1.869881
DnsApi                  2.240766
Kerberos                0.169989

The "Percent" output record field shows the ratio of the number of events logged by a source to the total number of events in the event log.

In this example, the calculation performed by the PROPCOUNT aggregate function is equivalent to executing the following two queries and calculating the ratio of the two aggregate functions for each event log source:

SELECT SourceName, COUNT(*) AS Numerator
FROM System
GROUP BY SourceName

SELECT COUNT(*) AS Denominator
FROM System

B. Using ON

The following query uses the IISW3C Input Format to parse IIS log files and calculate the percentage of hits for a page type and HTTP status code relative to the number of hits for that page type (i.e. the distribution of HTTP status codes within each page type):

SELECT EXTRACT_EXTENSION(cs-uri-stem) AS PageType, sc-status, MUL(PROPCOUNT(*) ON (PageType), 100.0) AS Hits
FROM ex040528.log 
GROUP BY PageType, sc-status
ORDER BY PageType, sc-status

A sample output of this query is:

PageType sc-status Hits
-------- --------- ----------
asp      200       100.000000
class    200       20.000000
class    304       80.000000
css      200       13.636364
css      304       45.454545
css      404       40.909091
dll      500       100.000000
exe      200       100.000000
gif      200       21.025641
gif      304       76.923077
gif      404       2.051282
htm      200       29.565217
htm      304       68.695652
htm      404       1.739130
html     404       100.000000
jpg      200       22.077922
jpg      304       77.922078
js       200       36.363636
js       304       63.636364
nsf      200       90.845070
nsf      302       0.704225
nsf      304       6.338028
nsf      403       2.112676
swf      200       27.272727
swf      304       72.727273

For each page type and HTTP status code, the "Hits" output record field shows the ratio of the number of requests for that page type and HTTP status code to the total number of requests for that page type.

In this example, the calculation performed by the PROPCOUNT aggregate function is equivalent to executing the following two queries and calculating the ratio of the two aggregate functions for each page type and HTTP status:

SELECT EXTRACT_EXTENSION(cs-uri-stem) AS PageType, sc-status, COUNT(*) AS Numerator
FROM ex040528.log 
GROUP BY PageType, sc-status
ORDER BY PageType, sc-status

SELECT EXTRACT_EXTENSION(cs-uri-stem) AS PageType, COUNT(*) AS Denominator
FROM ex040528.log 
GROUP BY PageType
ORDER BY PageType

See also:

Aggregate Functions

Aggregating Data Within Groups
Calculating Percentages

© 2004 Microsoft Corporation. All rights reserved.