PROPSUM

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

Arguments:

<field_expr>

The field-expression whose values are to be summed.
The field-expression data type must be INTEGER or REAL.

<on_field_expr_list>

List of GROUP BY field-expressions identifying the hierarchically higher group on which the denominator SUM 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 SUM aggregate function is calculated on the whole set of input records.

Return Type:

REAL

Remarks:

• When used without a GROUP BY clause, the PROPSUM 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 PROPSUM 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. PROPSUM

The following query uses the IISW3C Input Format to parse IIS log files and calculate the percentage of bytes sent for each page type:

SELECT EXTRACT_EXTENSION(cs-uri-stem) AS PageType, MUL(PROPSUM(sc-bytes), 100.0) AS PercentBytes
FROM ex040528.log
GROUP BY PageType

A sample output of this query is:

PageType PercentBytes
-------- ------------
htm	7.236737
css	1.035243
gif	23.772064
exe	1.398888
nsf	24.459391
swf	32.528669
jpg	8.003440
html	 0.104051
dll	0.002322
asp	0.000000
js	 1.260613
class	0.198582

The "PercentBytes" output record field shows the ratio of the bytes sent for each page type to the total number of bytes sent in the log.

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

SELECT EXTRACT_EXTENSION(cs-uri-stem) AS PageType, SUM(sc-bytes) AS Numerator
FROM ex040528.log
GROUP BY PageType

SELECT SUM(sc-bytes) AS Denominator
FROM ex040528.log

B. Using ON

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

SELECT EXTRACT_EXTENSION(cs-uri-stem) AS PageType, sc-status, MUL(PROPSUM(sc-bytes) ON (PageType), 100.0) AS PercentBytes
FROM ex040528.log 
GROUP BY PageType, sc-status
ORDER BY PageType, sc-status

A sample output of this query is:

PageType sc-status PercentBytes
-------- --------- ------------
asp	200	 0.000000
class	200	 92.591620
class	304	 7.408380
css	200	 6.039609
css	304	 3.502318
css	404	 90.458073
dll	500	 100.000000
exe	200	 100.000000
gif	200	 87.811668
gif	304	 6.935887
gif	404	 5.252445
htm	200	 92.926606
htm	304	 4.197755
htm	404	 2.875639
html	 404	 100.000000
jpg	200	 97.245679
jpg	304	 2.754321
js	 200	 97.963913
js	 304	 2.036087
nsf	200	 99.604883
nsf	302	 0.050656
nsf	304	 0.281114
nsf	403	 0.063347
swf	200	 99.910188
swf	304	 0.089812

For each page type and HTTP status code, the "PercentBytes" output record field shows the ratio of the response bytes for that page type and HTTP status code to the total response bytes for that page type.

In this example, the calculation performed by the PROPSUM 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, SUM(sc-bytes) AS Numerator
FROM ex040528.log 
GROUP BY PageType, sc-status
ORDER BY PageType, sc-status

SELECT EXTRACT_EXTENSION(cs-uri-stem) AS PageType, SUM(sc-bytes) AS Denominator
FROM ex040528.log 
GROUP BY PageType
ORDER BY PageType

C. PROPSUM, GROUP BY, and HAVING

The following query uses the IISW3C Input Format to parse IIS log files and return the page types that represent more than 10% of the total bytes sent:

SELECT EXTRACT_EXTENSION(cs-uri-stem) AS PageType
FROM ex040528.log 
GROUP BY PageType
HAVING PROPSUM(sc-bytes) > 0.1

See also:

Aggregate Functions

Aggregating Data Within Groups
Calculating Percentages

© 2004 Microsoft Corporation. All rights reserved.