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.