Make Aggregation Query Layer (Data Management)

Summary

Creates a query layer that summarizes, aggregates, and filters DBMS tables dynamically based on time, range, and attribute queries from a related table, and joins the result to a feature layer.

Learn more about aggregating values into related features

Usage

• Query layers will only work with enterprise databases. File geodatabases are not a valid input workspace for this tool.

• Aggregate results are always computed dynamically at the database level.

• The Output Layer value will consist of fields containing the result of the statistical operation. The count statistic is included in the ROW_COUNT field by default

• The statistical operations that are available with this tool are count, sum, average, minimum, maximum, and standard deviation.

• A field will be created for each statistic type using the following naming convention: COUNT_<field>, SUM_<field>, AVG_<field>, MIN_<field>, MAX_<field>, and STDDEV_<field>, (where <field> is the name of the input field for which the statistic is computed).

• The Related Join Field value is used in the Group By clause in the SQL statement generated by this tool. Statistics will be calculated separately for each unique attribute value from the Related Join Field value.

• The layer that is created by the tool is temporary and will not persist after the session ends unless the project is saved, the layer is saved to a layer file, or the data is persisted by making a copy using Copy Rows or Copy Features.

Parameters

Dialog

Label	Explanation	Data Type
Target Feature Class	The feature class or spatial table from an enterprise database.	Feature Class
Target Join Field	The field in the target feature class on which the join will be based.	Field
Related Table	The input table containing the fields that will be used to calculate statistics. Statistics are joined to the Output Layer value.	Table; Feature Class
Related Join Field	A field in the summary table that contains the values on which the join will be based. Aggregation or summary statistics are also calculated separately for each unique attribute value from this field.	Field
Output Layer	The output name of the query layer that will be created.	Feature Layer
Summary Field(s) (Optional)	Specifies the numeric field or fields containing the attribute values that will be used to calculate the specified statistic. Multiple statistic and field combinations can be specified. Null values are excluded from all statistical calculations. The output layer will include a ROW_COUNT field showing total count (or frequency) of each unique value from the Related Join Field value. The difference between the ROW_COUNT field and the Count statistic type is that ROW_COUNT includes null values while Count excludes null values. Available statistics types are as follows: Count—The number of values included in the statistical calculations will be found. Each value will be counted except null values. Sum—The values for the specified field will be added together. Average—The average for the specified field will be calculated. Minimum—The smallest value for all records of the specified field will be found. Maximum—The largest value for all records of the specified field will be found. Standard deviation—The standard deviation of values in the specified field will be calculated.	Value Table
Parameter Definitions (Optional)	Specifies one or more query parameters for criteria or conditions; records matching these criteria are used while computing aggregated results. A query parameter is similar to an SQL statement variable for which the value is defined when the query is run. This allows you to dynamically change query filters for the output layer. You can think of a parameter as a predicate or condition in a SQL where clause. For example Country_Name = 'Nigeria' in a SQL where clause is called a predicate in which the = is a comparison operator, Country_Name is a field name on the left, and 'Nigeria' is a value on the right. When you define more than one parameter, you must specify a logical operator between them (such as AND, OR, and so on). Learn more about defining parameters in a query layer When not specified, all records from the related table will be used in computing aggregated or summary results. The two parameter definition types are the following: Range—Connect numeric or temporal values dynamically to the range and time sliders. Discrete—Update a query with literal values when the query is run. The following properties are available: Parameter Type—The parameter type can be Range or Discrete. Name—The name of the parameter, which is similar to a variable name. A name cannot contain spaces or special characters. Once the output query layer is created and the layer source SQL statement is checked, this name in the SQL statement that defines the output query layer source,will be prefixed with either ::r: (for range parameter) or :: (for discrete parameter). Alias—The alias for the parameter name. The alias can include spaces and special characters. Field or Expression—A field name or a valid SQL expression that will be used in the left side of a predicate or condition in a where clause. Data type—The data type of the field or expression specified in the Field or Expression column. When the Parameter Type value is Range, the Data type column value cannot be String. Date—The data type of the field or expression will be Date (date time). String—The data type of the field or expression will be String (text). Integer—The data type of the field or expression will be Integer (whole numbers). Double—The data type of the field or expression will be Double (fractional numbers). Start Value—The default start value for the Range column. This is the value that will be used when the time or range slider is not enabled. When the Start Value and End Value column values are omitted and the time or range slider is disabled, all records from the related table will be used to compute aggregated results. This value is ignored when the Parameter Type column is set to Discrete. End Value—The default end value for the Range parameter. This is the value that will be used when the time or range slider is not enabled. When the Start Value and End Value column values are omitted and the time or range slider is disabled, all records from the related table will be used to compute aggregated results. This value is ignored when the Parameter Type column is set to Discrete. Operator for Discrete Parameter—The comparison operator that will be used between the Field or Expression column value and a value in an SQL predicate or condition. None—Choose None, when Parameter Type is set to Range. Equal to—Compare the equality of a field or expression to a value. Not equal to—Test whether a field or expression is not equal to a value. Greater than—Test whether a field or expression is higher than a value. Less than—Test whether a field or expression is lower than a value. Include values—Determine whether a value from a field or expression matches any value in a list. Default Discrete Values—When the Parameter Type value is Discrete, you must provide a default value. When Operator for Discrete Parameter is Include values, you can provide multiple values separated by commas, for example VANDALISM,BURGLARY/THEFT. Operator for Next Parameter—The logical operator between this operator and the next one. This column is only applicable when you have more than one parameter definition. None—Choose None when there are no more parameters. And—Combine two conditions and select a record if both conditions are true. Or—Combine two conditions and select a record if at least one condition is true.	Value Table
Unique Identifier Field(s) (Optional)	The unique identifier fields that will be used to uniquely identify each row in the table.	String
Shape Type (Optional)	Specifies the shape type of the query layer. Only those records from the result set of the query that match the specified shape type will be used in the output query layer. By default, the shape type of the first record in the result set will be used. This parameter is ignored if the result set of the query does not return a geometry field. Point—The output query layer will use point geometry. Multipoint—The output query layer will use multipoint geometry. Polygon—The output query layer will use polygon geometry. Polyline—The output query layer will use polyline geometry.	String
Spatial Reference ID (SRID) (Optional)	The spatial reference identifier (SRID) value for queries that return geometry. Only those records from the result set of the query that match the specified SRID value will be used in the output query layer. By default, the SRID value of the first record in the result set will be used. This parameter is ignored if the result set of the query does not return a geometry field.	String
Coordinate System (Optional)	The coordinate system that will be used by the output query layer. By default, the spatial reference of the first record in the result set will be used. This parameter is ignored if the result set of the query does not return a geometry field.	Spatial Reference
Coordinates include M values (Optional)	Specifies whether the output layer will include linear measurements (m-values). Checked—The layer will include m-values. Unchecked—The layer will not include m-values. This is the default.	Boolean
Coordinates include Z values (Optional)	Specifies whether the output layer will include elevation values (z-values). Checked—The layer will include z-values. Unchecked—The layer will not include z-values. This is the default.	Boolean
Extent (Optional)	Specifies the extent of the layer. The extent must include all features in the table. Current Display Extent —The extent will be based on the active map or scene. Draw Extent —The extent will be based on a rectangle drawn on the map or scene. Extent of a Layer —The extent will be based on an active map layer. Choose an available layer or use the Extent of data in all layers option. Each map layer has the following options: All Features —The extent of all features. Selected Features —The extent of the selected features. Visible Features —The extent of visible features. Browse —The extent will be based on a dataset. Clipboard —The extent can be copied to and from the clipboard. Copy Extent —Copies the extent and coordinate system to the clipboard. Paste Extent —Pastes the extent and coordinate system from the clipboard. If the clipboard does not include a coordinate system, the extent will use the map’s coordinate system. Reset Extent —The extent will be reset to the default value. When coordinates are manually provided, the coordinates must be numeric values and in the active map's coordinate system. The map may use different display units than the provided coordinates. Use a negative value sign for south and west coordinates.	Extent

Python

arcpy.management.MakeAggregationQueryLayer(target_feature_class, target_join_field, related_table, related_join_field, out_layer, {statistics}, {parameter_definitions}, {oid_fields}, {shape_type}, {srid}, {spatial_reference}, {m_values}, {z_values}, {extent})

Name	Explanation	Data Type
target_feature_class	The feature class or spatial table from an enterprise database.	Feature Class
target_join_field	The field in the target feature class on which the join will be based.	Field
related_table	The input table containing the fields that will be used to calculate statistics. Statistics are joined to the out_layer value.	Table; Feature Class
related_join_field	A field in the summary table that contains the values on which the join will be based. Aggregation or summary statistics are also calculated separately for each unique attribute value from this field.	Field
out_layer	The output name of the query layer that will be created.	Feature Layer
statistics [[statistic_type, field],...] (Optional)	Specifies the numeric field or fields containing the attribute values that will be used to calculate the specified statistic. Multiple statistic and field combinations can be specified. Null values are excluded from all statistical calculations. The output layer will include a ROW_COUNT field showing total count (or frequency) of each unique value from the related_join_field value. The difference between the ROW_COUNT field and the COUNT statistic type is that ROW_COUNT includes null values while COUNT excludes null values. COUNT—The number of values included in the statistical calculations will be found. Each value will be counted except null values. SUM—The values for the specified field will be added together. AVG—The average for the specified field will be calculated. MIN—The smallest value for all records of the specified field will be found. MAX—The largest value for all records of the specified field will be found. STDDEV—The standard deviation of values in the specified field will be calculated.	Value Table
parameter_definitions [[parameter_def_type, name, alias, field_or_expression, data_type, start_value, end_value, operator, default_value, operator_for_next_parameter],...] (Optional)	Specifies one or more query parameters for criteria or conditions; records matching these criteria are used while computing aggregated results. A query parameter is similar to an SQL statement variable for which the value is defined when the query is run. This allows you to dynamically change query filters for the output layer. You can think of a parameter as a predicate or condition in a SQL where clause. For example Country_Name = 'Nigeria' in a SQL where clause is called a predicate in which the = is a comparison operator, Country_Name is a field name on the left, and 'Nigeria' is a value on the right. When you define more than one parameter, you must specify a logical operator between them (such as AND, OR, and so on). Learn more about defining parameters in a query layer When not specified, all records from the related table will be used in computing aggregated or summary results. The two parameter definition types are the following: Range—Connect numeric or temporal values dynamically to the range and time sliders. Discrete—Update a query with literal values when the query is run. The following properties are available: Parameter Type—The parameter type can be RANGE or DISCRETE. Name—The name of the parameter, which is similar to a variable name. A name cannot contain spaces or special characters. Once the output query layer is created and the layer source SQL statement is checked, this name in the SQL statement that defines the output query layer source,will be prefixed with either ::r: (for range parameter) or :: (for discrete parameter). Alias—The alias for the parameter name. The alias can include spaces and special characters. Field or Expression—A field name or a valid SQL expression that will be used in the left side of a predicate or condition in a where clause. Data type—The data type of the field or expression specified in the Field or Expression column. When the Parameter Type value is RANGE, the Data type column value cannot be STRING. DATE—The data type of the field or expression will be Date (date time). STRING—The data type of the field or expression will be String (text). INTEGER—The data type of the field or expression will be Integer (whole numbers). DOUBLE—The data type of the field or expression will be Double (fractional numbers). Start Value—The default start value for the RANGE column. This is the value that will be used when the time or range slider is not enabled. When the Start Value and End Value column values are omitted and the time or range slider is disabled, all records from the related table will be used to compute aggregated results. This value is ignored when the Parameter Type column is set to DISCRETE. End Value—The default end value for the RANGE parameter. This is the value that will be used when the time or range slider is not enabled. When the Start Value and End Value column values are omitted and the time or range slider is disabled, all records from the related table will be used to compute aggregated results. This value is ignored when the Parameter Type column is set to DISCRETE. Operator for Discrete Parameter—The comparison operator that will be used between the Field or Expression column value and a value in an SQL predicate or condition. NONE—Choose NONE when Parameter Type is set to RANGE. EQUAL TO—Compare the equality of a field or expression to a value. NOT EQUAL TO—Test whether a field or expression is not equal to a value. GREATER THAN—Test whether a field or expression is higher than a value. LESS THAN—Test whether a field or expression is lower than a value. INCLUDE VALUES—Determine whether a value from a field or expression matches any value in a list. Default Discrete Values—When the Parameter Type value is DISCRETE, you must provide a default value. When Operator for Discrete Parameter is INCLUDE VALUES, you can provide multiple values separated by commas, for example VANDALISM,BURGLARY/THEFT. Operator for Next Parameter—The logical operator between this operator and the next one. This column is only applicable when you have more than one parameter definition. NONE—Choose NONE when there are no more parameters. AND—Combine two conditions and select a record if both conditions are true. OR—Combine two conditions and select a record if at least one condition is true.	Value Table
oid_fields [oid_fields,...] (Optional)	The unique identifier fields that will be used to uniquely identify each row in the table.	String
shape_type (Optional)	Specifies the shape type of the query layer. Only those records from the result set of the query that match the specified shape type will be used in the output query layer. By default, the shape type of the first record in the result set will be used. This parameter is ignored if the result set of the query does not return a geometry field. POINT—The output query layer will use point geometry. MULTIPOINT—The output query layer will use multipoint geometry. POLYGON—The output query layer will use polygon geometry. POLYLINE—The output query layer will use polyline geometry.	String
srid (Optional)	The spatial reference identifier (SRID) value for queries that return geometry. Only those records from the result set of the query that match the specified SRID value will be used in the output query layer. By default, the SRID value of the first record in the result set will be used. This parameter is ignored if the result set of the query does not return a geometry field.	String
spatial_reference (Optional)	The coordinate system that will be used by the output query layer. By default, the spatial reference of the first record in the result set will be used. This parameter is ignored if the result set of the query does not return a geometry field.	Spatial Reference
m_values (Optional)	Specifies whether the output layer will include linear measurements (m-values). INCLUDE_M_VALUES—The layer will include m-values. DO_NOT_INCLUDE_M_VALUES—The layer will not include m-values. This is the default.	Boolean
z_values (Optional)	Specifies whether the output layer will include elevation values (z-values). INCLUDE_Z_VALUES—The layer will include z-values. DO_NOT_INCLUDE_Z_VALUES—The layer will not include z-values. This is the default.	Boolean
extent (Optional)	Specifies the extent of the layer. The extent must include all features in the table. MAXOF—The maximum extent of all inputs will be used. MINOF—The minimum area common to all inputs will be used. DISPLAY—The extent is equal to the visible display. Layer name—The extent of the specified layer will be used. Extent object—The extent of the specified object will be used. Space delimited string of coordinates—The extent of the specified string will be used. Coordinates are expressed in the order of x-min, y-min, x-max, y-max.	Extent

Code sample

MakeAggregationQueryLayer example 1 (Python window)

The following Python window script demonstrates how to compute total crimes for each district from a point feature class, and join the result to a police district feature class.

import arcpy
arcpy.env.workspace = "C:/data/localhost.sde"
arcpy.management.MakeAggregationQueryLayer(
    "PoliceDistricts", "district", "Crime_locations", "PdDistrict", "SF_Crimes")

MakeAggregationQueryLayer example 2 (Python window)

The following Python window script demonstrates how to compute total crimes for each district and for each crime type from a point feature class, and join the result to a police district feature class.

import arcpy
arcpy.env.workspace = "C:/data/localhost.sde"
arcpy.management.MakeAggregationQueryLayer(
    "PoliceDistricts", "district", "Crime_locations", "PdDistrict", "SF_Crimes", 
    None, 
    "DISCRETE crime_type # Category STRING # # 'INCLUDE VALUES' 'VANDALISM, BURGLARY/THEFT' NONE")

MakeAggregationQueryLayer example 3 (Python window)

The following Python window script demonstrates how to compute total and average rainfall from a time series table and join the result to a water stations feature class.

import arcpy
arcpy.env.workspace = "C:/data/localhost.sde"
arcpy.management.MakeAggregationQueryLayer(
    "weather_stations", "station_id", "observed_rainfall", "station_id", 
    "Total_Rainfall", [["SUM", "rainfall_inch"], ["MIN", "rainfall_inch"]])

MakeAggregationQueryLayer example 4 (Python window)

The following Python window script demonstrates how to compute total and average rainfall from a time series table for any time window and join the result to a water stations feature class.

import arcpy
arcpy.env.workspace = "C:/data/localhost.sde"
arcpy.management.MakeAggregationQueryLayer(
    "weather_stations", "station_id", "observed_rainfall", "station_id", 
    "Total_Rainfall", [["SUM", "rainfall_inch"], ["MIN", "rainfall_inch"]], 
    "RANGE TimeVar # collection_date DATE 1/1/2020 12/1/2020 NONE # NONE")

Environments

Current Workspace, Output Coordinate System