PROPSUM

PROPSUM ( <field_expr> ) [ ON ( <on_field_expr_list> ) ]
<on_field_expr_list>	::=	<field_expr> [ , <field_expr> ... ]

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:

COUNT
SUM
AVG
MAX
MIN
PROPCOUNT
GROUPING

Aggregate Functions

Aggregating Data Within Groups
Calculating Percentages

---

© 2004 Microsoft Corporation. All rights reserved.