{"revision":"c10276e953456835cdbb1f3a60d58ae1980be353","document":{"id":1000,"path":"api-reference/server-api-reference/c_GlideAggregateScopedAPI.md","title":"GlideAggregate - Scoped","bundle":null,"doc_type":null,"release":"australia","body":"\n# GlideAggregate- Scoped\n\nThe GlideAggregate API enables creating database aggregation queries.\n\nThe GlideAggregate class is an extension of the GlideRecord class and provides database aggregation \\(AVG, COUNT, GROUP\\_CONCAT, GROUP\\_CONCAT\\_DISTINCT, MAX, MIN, STDDEV, SUM\\) queries. This functionality can be helpful when creating customized reports or in calculations for calculated fields.\n\nWhen you use GlideAggregate methods on currency or price fields, you are working with the reference currency value. Be sure to convert the aggregate values to the user's session currency for display. Because the conversion rate between the currency or price value \\(displayed value\\) and its reference [currency](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/platform-administration/currency.md) value \\(aggregation value\\) might change, the result may not be what the user expects.\n\n**Note:** When using an on-premise system, the database server time zone must be set to GMT/UTC for this class to work properly.\n\n**Parent Topic:**[Server API reference](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/api-reference/server-api-reference/api-server.md)\n\n## Scoped GlideAggregate - GlideAggregate\\(String tableName\\)\n\nCreates a GlideAggregate object on the specified table.\n\n|Name|Type|Description|\n|----|----|-----------|\n|tableName|String|Name of the table.|\n\n```\nvar count = new GlideAggregate('incident');\n```\n\n## Scoped GlideAggregate - addAggregate\\(String agg, String name\\)\n\nAdds an aggregate to a database query.\n\n\n
\n\nName\n\n\n\nType\n\n\n\nDescription\n\n
\n\nagg\n\n\n\nString\n\n\n\nName of an aggregate to use.Valid values:\n\n- AVG: Average value of the expression.\n- COUNT: Count of the number of non-null values.\n- GROUP\\_CONCAT: Concatenates all non-null values of the group in ascending order, joins them with a comma \\(','\\), and returns the result as a string.\n- GROUP\\_CONCAT\\_DISTINCT: Concatenates all non-null values of the group in ascending order, removes duplicates, joins them with a comma \\(','\\), and returns the result as a string.\n- MAX: Largest, or maximum, value.\n- MIN: Minimum value.\n- STDDEV: Population standard deviation.\n- SUM: Sum of all values.\n\n
\n\nname\n\n\n\nString\n\n\n\nOptional. Name of the field to group the results of the aggregation by.Default: Null\n\n
|Type|Description|\n|----|-----------|\n|None| |\n\nThe following example shows how to use GlideAggregate functions on the Incident \\[incident\\] table.\n\n```\nvar incidentGA = new GlideAggregate('incident');\nincidentGA.groupBy('category');\nincidentGA.orderByAggregate('COUNT', 'sys_mod_count');\n\nincidentGA.addAggregate('AVG', 'sys_mod_count');\nincidentGA.addAggregate('COUNT', 'sys_mod_count');\nincidentGA.addAggregate('GROUP_CONCAT', 'sys_mod_count');\nincidentGA.addAggregate('GROUP_CONCAT_DISTINCT', 'sys_mod_count');\nincidentGA.addAggregate('MAX', 'sys_mod_count');\nincidentGA.addAggregate('MIN', 'sys_mod_count');\nincidentGA.addAggregate('STDDEV', 'sys_mod_count');\nincidentGA.addAggregate('SUM', 'sys_mod_count');\n\nincidentGA.query();\n\nwhile (incidentGA.next()) {\ngs.info('CATEGORY: ' + incidentGA.getValue('category'));\ngs.info('AVG: ' + incidentGA.getAggregate('AVG', 'sys_mod_count'));\ngs.info('COUNT: ' + incidentGA.getAggregate('COUNT', 'sys_mod_count'));\ngs.info('GROUP_CONCAT: ' + incidentGA.getAggregate('GROUP_CONCAT', 'sys_mod_count'));\ngs.info('GROUP_CONCAT_DISTINCT: ' + incidentGA.getAggregate('GROUP_CONCAT_DISTINCT', 'sys_mod_count'));\ngs.info('MAX: ' + incidentGA.getAggregate('MAX', 'sys_mod_count'));\ngs.info('MIN: ' + incidentGA.getAggregate('MIN', 'sys_mod_count'));\ngs.info('STDDEV: ' + incidentGA.getAggregate('STDDEV', 'sys_mod_count'));\ngs.info('SUM: ' + incidentGA.getAggregate('SUM', 'sys_mod_count'));\ngs.info(' ');\n}\n```\n\nOutput.\n\n```\nCATEGORY: inquiry\nAVG: 8.42424242424242424242424242424242424242E00\nCOUNT: 33\nGROUP_CONCAT: 0,0,0,0,1,2,2,4,4,4,5,5,5,6,6,6,6,6,6,6,6,7,8,8,8,8,9,15,15,19,32,33,36\nGROUP_CONCAT_DISTINCT: 0,1,2,4,5,6,7,8,9,15,19,32,33,36\nMAX: 36\nMIN: 0\nSTDDEV: 9.15843294125113629932710147135171494439E00\nSUM: 278\n \nCATEGORY: software\nAVG: 21\nCOUNT: 13\nGROUP_CONCAT: 3,4,5,5,6,7,9,10,10,11,14,94,95\nGROUP_CONCAT_DISTINCT: 3,4,5,6,7,9,10,11,14,94,95\nMAX: 95\nMIN: 3\nSTDDEV: 3.27693962918655755532970326989852794087E01\nSUM: 273\n \nCATEGORY: Hardware\nAVG: 6.8\nCOUNT: 10\nGROUP_CONCAT: 2,5,5,6,6,6,7,7,9,15\nGROUP_CONCAT_DISTINCT: 2,5,6,7,9,15\nMAX: 15\nMIN: 2\nSTDDEV: 3.39280283999985933622820108982884699755E00\nSUM: 68\n \nCATEGORY: network\nAVG: 18\nCOUNT: 5\nGROUP_CONCAT: 3,12,17,21,37\nGROUP_CONCAT_DISTINCT: 3,12,17,21,37\nMAX: 37\nMIN: 3\nSTDDEV: 1.25698050899765347157025586536136512302E01\nSUM: 90\n \nCATEGORY: \nAVG: 9.5\nCOUNT: 4\nGROUP_CONCAT: 8,8,10,12\nGROUP_CONCAT_DISTINCT: 8,10,12\nMAX: 12\nMIN: 8\nSTDDEV: 1.91485421551267621995020382273964310607E00\nSUM: 38\n \nCATEGORY: database\nAVG: 29\nCOUNT: 2\nGROUP_CONCAT: 8,50\nGROUP_CONCAT_DISTINCT: 8,50\nMAX: 50\nMIN: 8\nSTDDEV: 2.969848480983499602483546320840365965E01\nSUM: 58\n```\n\n## Scoped GlideAggregate - addEncodedQuery\\(String query\\)\n\nAdds an encoded query to the other queries that may have been set for this aggregate.\n\n|Name|Type|Description|\n|----|----|-----------|\n|query|String|An encoded query to add to the aggregate.|\n\n|Type|Description|\n|----|-----------|\n|None| |\n\n```\n//Number of incidents varies depending on the current state\n//of the incident table\nvar count = new GlideAggregate('incident');\ncount.addEncodedQuery('active=true');\ncount.addAggregate('COUNT');\ncount.query();\nvar incidents = 0;\nif (count.next())\n incidents = count.getAggregate('COUNT');\ngs.info(incidents);\n```\n\n## Scoped GlideAggregate - addQuery\\(String name, String operator, String value\\)\n\nAdds a query to the aggregate.\n\n|Name|Type|Description|\n|----|----|-----------|\n|name|String|Query to add.|\n|operator|String|Operator for the query.|\n|value|String|List of values to include in the query.|\n\n|Type|Description|\n|----|-----------|\n|GlideQueryCondition|Query condition.|\n\n```\n//Number of incidents varies depending on the current state\n//of the incident table\nvar count = new GlideAggregate('incident');\ncount.addQuery('active', '=','true');\ncount.addAggregate('COUNT', 'category');\ncount.query(); \nwhile (count.next()) {\n var category = count.category;\n var categoryCount = count.getAggregate('COUNT', 'category');\n gs.info(\"There are currently \" + categoryCount + \" incidents with a category of \" + category);\n}\n```\n\nOutput:\n\n```\nThere are currently 1 incidents with a category of database\nThere are currently 5 incidents with a category of hardware\nThere are currently 42 incidents with a category of inquiry\nThere are currently 4 incidents with a category of network\nThere are currently 4 incidents with a category of request\nThere are currently 7 incidents with a category of software\n```\n\n## Scoped GlideAggregate - addNotNullQuery\\(String fieldName\\)\n\nAdds a not null query to the aggregate.\n\n|Name|Type|Description|\n|----|----|-----------|\n|fieldname|String|Name of the field.|\n\n|Type|Description|\n|----|-----------|\n|GlideQueryCondition|Scoped query condition.|\n\n```\nvar count = new GlideAggregate('incident');\n count.addNotNullQuery('short_description');\n count.query(); // Issue the query to the database to get all records\n while (count.next()) { \n // add code here to process the aggregate\n }\n```\n\n## Scoped GlideAggregate - addNullQuery\\(String fieldName\\)\n\nAdds a null query to the aggregate.\n\n|Name|Type|Description|\n|----|----|-----------|\n|fieldName|String|Name of the field.|\n\n|Type|Description|\n|----|-----------|\n|GlideQueryCondition|The scoped query condition.|\n\n```\nvar count = new GlideAggregate('incident');\n count.addNullQuery('short_description');\n count.query(); // Issue the query to the database to get all records\n while (count.next()) { \n // add code here to process the aggregate\n }\n```\n\n## Scoped GlideAggregate - addTrend\\(String fieldName, String timeInterval, Number numUnits\\)\n\nAdds a trend for a specified field. Use a trend to show patterns over a period of time.\n\n**Note:** To control whether to group **dayofweek** results by year, use [Scoped GlideAggregate - setIntervalYearIncluded\\(Boolean b\\)](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/api-reference/server-api-reference/c_GlideAggregateScopedAPI.md).\n\n\n
\n\nName\n\n\n\nType\n\n\n\nDescription\n\n
\n\nfieldName\n\n\n\nString\n\n\n\nName of the field for which trending should occur.\n\n
\n\ntimeInterval\n\n\n\nString\n\n\n\nTime interval for the trend. Valid values:\n\n- date\n- dayofweek\n- hour\n- minute\n- quarter\n- value\n- week\n- year\n\n
\n\nnumUnits\n\n\n\nNumber\n\n\n\nOptional. Only valid when **timeInterval** = minute. Number of minutes to include in the trend.Default: 1\n\n
|Type|Description|\n|----|-----------|\n|void| |\n\n```\nvar ga = new GlideAggregate('incident');\nga.addAggregate('COUNT'); // Count all incidents opened each quarter\nga.addTrend('opened_at', 'quarter');\nga.query();\nwhile(ga.next()) {\n gs.info([ga.getValue('timeref'), ga.getAggregate('COUNT')]);\n}\n```\n\nOutput:\n\n```\n3/2018, 9\n4/2018, 2\n1/2019, 38\n2/2019, 310\n```\n\n## Scoped GlideAggregate - getAggregate\\(String agg, String name\\)\n\nReturns the value of an aggregate from the current record.\n\n\n
\n\nName\n\n\n\nType\n\n\n\nDescription\n\n
\n\nagg\n\n\n\nString\n\n\n\nType of the aggregate.Valid values:\n\n- AVG: Average value of the expression.\n- COUNT: Count of the number of non-null values.\n- GROUP\\_CONCAT: Concatenates all non-null values of the group in ascending order, joins them with a comma \\(','\\), and returns the result as a string.\n- GROUP\\_CONCAT\\_DISTINCT: Concatenates all non-null values of the group in ascending order, removes duplicates, joins them with a comma \\(','\\), and returns the result as a string.\n- MAX: Largest, or maximum, value.\n- MIN: Minimum value.\n- STDDEV: Population standard deviation.\n- SUM: Sum of all values.\n\n
\n\nname\n\n\n\nString\n\n\n\nName of the field on which to perform the aggregation.\n\n
\n
\n\nType\n\n\n\nDescription\n\n
\n\nString\n\n\n\nThe value of the aggregation.If the values being aggregated are FX Currency values, the returned value is in the format ``, such as: `USD;134.980000`.\n\n**Note:** If the specified field contains FX Currency values of mixed currency types, the method is not able to aggregate the values and returns a semicolon \\(;\\).\n\n
Shows an aggregation that returns the number of records in the Incident table.\n\n```\nvar count = new GlideAggregate('incident');\ncount.addAggregate('COUNT');\ncount.query();\nvar incidents = 0;\nif (count.next()) {\n incidents = count.getAggregate('COUNT');\n}\n//Number of incidents varies depending on the current state\n//of the incident table\ngs.info('Number of incidents: ' + incidents);\n```\n\nOutput: Number of incidents: 63.\n\nShows the aggregation of an FX Currency field.\n\n```\nvar ga = new GlideAggregate('laptop_tracker');\nga.addAggregate('SUM', 'cost');\nga.groupBy('name');\nga.query();\nwhile (ga.next()) {\n gs.info('Aggregate results ' + ga.getValue('name') + ' => ' + ga.getAggregate('SUM', 'cost'));\n}\n```\n\nOutput:\n\n```\n*** Script: Aggregate results Apple MacBook Air => USD;1651.784280000000\n*** Script: Aggregate results Apple MacBook Pro => USD;1651.784280000000\n*** Script: Aggregate results Dell XPS => USD;470.852672000000\n*** Script: Aggregate results LG =>\n*** Script: Aggregate results Samsung Galaxy => USD;225.320000000000\n*** Script: Aggregate results Surface3 => USD;2895.560369520000\n*** Script: Aggregate results Toshiba => USD;9385.202875800000\n```\n\n## Scoped GlideAggregate - getAggregateEncodedQuery\\(\\)\n\nGets the query necessary to return the current aggregate.\n\n|Name|Type|Description|\n|----|----|-----------|\n|None| | |\n\n|Type|Description|\n|----|-----------|\n|String|Encoded query to get the aggregate.|\n\n```\nvar count = new GlideAggregate('incident');\ncount.addAggregate('MIN', 'sys_mod_count');\ncount.groupBy('category');\ncount.query(); \nwhile (count.next()) {\n gs.info(count.getAggregateEncodedQuery());\n}\n```\n\nOutput:\n\n```\ncategory=database\ncategory=hardware\ncategory=inquiry\ncategory=network\ncategory=request\ncategory=software\n```\n\n## Scoped GlideAggregate - getEncodedQuery\\(\\)\n\nRetrieves the encoded query.\n\n|Name|Type|Description|\n|----|----|-----------|\n|none| | |\n\n|Type|Description|\n|----|-----------|\n|String|Encoded query.|\n\n```\nvar count = new GlideAggregate('incident');\ncount.addAggregate('MIN', 'sys_mod_count');\ncount.addAggregate('MAX', 'sys_mod_count');\ncount.addAggregate('AVG', 'sys_mod_count');\ncount.groupBy('category');\ncount.query();\ngs.info(count.getEncodedQuery());\n```\n\nOutput:\n\n```\nORDERBYcategory^GROUPBYcategory\n```\n\n## Scoped GlideAggregate - getRowCount\\(\\)\n\nRetrieves the number of rows in the GlideAggregate object.\n\n|Name|Type|Description|\n|----|----|-----------|\n|none| | |\n\n|Type|Description|\n|----|-----------|\n|Number|Number of rows in the GlideAggregate object.|\n\n```\nvar count = new GlideAggregate('incident');\n count.addAggregate('MIN', 'sys_mod_count');\n count.addAggregate('MAX', 'sys_mod_count');\n count.addAggregate('AVG', 'sys_mod_count');\n count.groupBy('category');\n count.query();\n gs.info(count.getRowCount());\n while (count.next()) { \n var min = count.getAggregate('MIN', 'sys_mod_count');\n var max = count.getAggregate('MAX', 'sys_mod_count');\n var avg = count.getAggregate('AVG', 'sys_mod_count');\n var category = count.category.getDisplayValue();\n gs.info(category + \" Update counts: MIN = \" + min + \" MAX = \" + max + \" AVG = \" + avg);\n }\n```\n\nOutput:\n\n```\n6\nDatabase Update counts: MIN = 8 MAX = 48 AVG = 28.0000\nHardware Update counts: MIN = 4 MAX = 14 AVG = 6.6250\nInquiry / Help Update counts: MIN = 0 MAX = 34 AVG = 6.5714\nNetwork Update counts: MIN = 3 MAX = 37 AVG = 18.6000\nRequest Update counts: MIN = 5 MAX = 39 AVG = 13.4000\nSoftware Update counts: MIN = 4 MAX = 98 AVG = 24.0000\n```\n\n## Scoped GlideAggregate - getTableName\\(\\)\n\nRetrieves the table name associated with this GlideAggregate object.\n\n|Name|Type|Description|\n|----|----|-----------|\n|none| | |\n\n|Type|Description|\n|----|-----------|\n|String|Table name.|\n\n```\nvar count = new GlideAggregate('incident');\ncount.addAggregate('MIN', 'sys_mod_count');\ncount.addAggregate('MAX', 'sys_mod_count');\ncount.addAggregate('AVG', 'sys_mod_count');\ncount.groupBy('category');\ncount.query();\ngs.info(count.getTableName());\n```\n\n## Scoped GlideAggregate - getValue\\(String name\\)\n\nReturns the value of the specified field.\n\n|Name|Type|Description|\n|----|----|-----------|\n|name|String|Name of the field within the current table to return.|\n\n|Type|Description|\n|----|-----------|\n|String|Value of the specified field. Returns null if invalid \\(not part of result set\\).|\n\n```\nvar count = new GlideAggregate('incident');\ncount.addAggregate('MAX', 'sys_mod_count');\ncount.groupBy('category');\ncount.query(); \nwhile (count.next()) { \n gs.info(count.getValue('category') + \" category had \" + count.getAggregate('MAX', 'sys_mod_count') + \" updates\"); \n}\n```\n\nOutput:\n\n```\ncategory had 12 updates\nhardware category had 15 updates\ninquiry category had 36 updates\nnetwork category had 37 updates\nsoftware category had 95 updates\n```\n\n## Scoped GlideAggregate - groupBy\\(String name\\)\n\nProvides the name of a field to use in grouping the aggregates.\n\nMay be called numerous times to set multiple group fields.\n\n|Name|Type|Description|\n|----|----|-----------|\n|name|String|Name of the field.|\n\n|Type|Description|\n|----|-----------|\n|None| |\n\n```\nvar count = new GlideAggregate('incident');\ncount.addAggregate('MIN', 'sys_mod_count');\ncount.addAggregate('MAX', 'sys_mod_count');\ncount.addAggregate('AVG', 'sys_mod_count');\ncount.groupBy('category');\ncount.query(); \nwhile (count.next()) { \n var min = count.getAggregate('MIN', 'sys_mod_count');\n var max = count.getAggregate('MAX', 'sys_mod_count');\n var avg = count.getAggregate('AVG', 'sys_mod_count');\n var category = count.category.getDisplayValue();\n gs.info(category + \" Update counts: MIN = \" + min + \" MAX = \" + max + \" AVG = \" + avg);\n}\n```\n\nOutput:\n\n```\nDatabase Update counts: MIN = 8 MAX = 48 AVG = 28.0000\nHardware Update counts: MIN = 4 MAX = 14 AVG = 6.6250\nInquiry / Help Update counts: MIN = 0 MAX = 34 AVG = 6.5714\nNetwork Update counts: MIN = 3 MAX = 37 AVG = 18.6000\nRequest Update counts: MIN = 5 MAX = 39 AVG = 13.4000\nSoftware Update counts: MIN = 4 MAX = 98 AVG = 24.0000\n\n```\n\n## Scoped GlideAggregate - hasNext\\(\\)\n\nDetermines if there are any more records in the GlideAggregate object.\n\n|Name|Type|Description|\n|----|----|-----------|\n|none| | |\n\n\n
\n\nType\n\n\n\nDescription\n\n
\n\nBoolean\n\n\n\nFlag that indicates whether there are more results in the query set.\n\n Possible values:\n\n- true: More results are in the query set.\n- false: No more results in the query set.\n\n
```\nvar agg = new GlideAggregate('incident');\nagg.addAggregate('AVG', 'sys_mod_count');\nagg.groupBy('category');\nagg.query();\nwhile (agg.hasNext()) {\n agg.next();\n var avg = agg.getAggregate('AVG', 'sys_mod_count');\n var category = agg.category.getDisplayValue();\n gs.info(category + ': AVG = ' + avg);\n}\n```\n\nOutput:\n\n```\nDatabase: AVG = 32.5000\nHardware: AVG = 12.0000\nInquiry / Help: AVG = 7.6667\nNetwork: AVG = 24.0000\nRequest: AVG = 16.4000\nSoftware: AVG = 27.0833\n```\n\n## Scoped GlideAggregate - next\\(\\)\n\nMoves to the next record in the GlideAggregate.\n\n|Name|Type|Description|\n|----|----|-----------|\n|none| | |\n\n\n
\n\nType\n\n\n\nDescription\n\n
\n\nBoolean\n\n\n\nFlag that indicates whether there are more results in the query set.\n\n Possible values:\n\n- true: More results are in the query set.\n- false: No more results in the query set.\n\n
```\nvar count = new GlideAggregate('incident');\ncount.addAggregate('COUNT');\ncount.query();\nvar incidents = 0;\nif (count.next()) {\n incidents = count.getAggregate('COUNT');\n gs.info(incidents);\n}\n```\n\n## Scoped GlideAggregate - orderBy\\(String name\\)\n\nOrders the aggregates using the value of the specified field. The field is also added to the group-by list.\n\n\n
\n\nName\n\n\n\nType\n\n\n\nDescription\n\n
\n\nname\n\n\n\nString\n\n\n\nName of the field to order the aggregates by.Alternatively, you can provide a glidefunction to order the aggregates, such as `glidefunction:length(short_description)`. For more information about glidefunctions, see [glidefunction operations](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/platform-administration/platform-support-functions.md).\n\n
|Type|Description|\n|----|-----------|\n|None| |\n\n```\nvar agg = new GlideAggregate('incident');\nagg.addAggregate('count', 'category'); \nagg.orderBy('category'); \nagg.query(); \nwhile (agg.next()) { \n var category = agg.category;\n var count = agg.getAggregate('count', 'category');\n var agg2 = new GlideAggregate('incident'); \n agg2.addAggregate('count', 'category');\n agg2.orderBy('category');\n gs.info(category + \": Current number of incidents:\" + count);\n}\n```\n\nOutput:\n\n```\ndatabase: Current number of incidents:2\nhardware: Current number of incidents:8\ninquiry: Current number of incidents:28\nnetwork: Current number of incidents:5\nrequest: Current number of incidents:5\nsoftware: Current number of incidents:11\n```\n\n## Scoped GlideAggregate - orderByAggregate\\(String agg, String fieldName\\)\n\nOrders the aggregates based on the specified aggregate and field.\n\n|Name|Type|Description|\n|----|----|-----------|\n|agg|String|Type of aggregation.|\n|fieldName|String|Name of the field to aggregate.|\n\n|Type|Description|\n|----|-----------|\n|void| |\n\n```\nga.addAggregate('COUNT', 'category');\nga.orderByAggregate('count', 'category');\nga.query();\nwhile(ga.next()) {\n gs.info('Category: ' + ga.category + ' ' + ga.getAggregate('COUNT', 'category'));\n}\n```\n\nOutput:\n\n```\nCategory: inquiry 18\nCategory: software 11\nCategory: hardware 7\nCategory: network 5\nCategory: request 5\nCategory: 4\nCategory: database 2\n```\n\n## Scoped GlideAggregate - orderByDesc\\(String name\\)\n\nSorts the aggregates in descending order based on the specified field. The field will also be added to the group-by list.\n\n|Name|Type|Description|\n|----|----|-----------|\n|name|String|Name of the field.|\n\n|Type|Description|\n|----|-----------|\n|None| |\n\n```\nvar agg = new GlideAggregate('incident');\nagg.addAggregate('count', 'category'); \nagg.orderByDesc('category'); \nagg.query(); \nwhile (agg.next()) { \n var category = agg.category;\n var count = agg.getAggregate('count', 'category');\n var agg2 = new GlideAggregate('incident'); \n agg2.addAggregate('count', 'category');\n agg2.orderBy('category');\n gs.info(category + \": Current number of incidents:\" + count);\n}\n```\n\nOutput:\n\n```\nsoftware: Current number of incidents:11\nrequest: Current number of incidents:5\nnetwork: Current number of incidents:5\ninquiry: Current number of incidents:28\nhardware: Current number of incidents:8\ndatabase: Current number of incidents:2\n\n```\n\n## Scoped GlideAggregate - query\\(\\)\n\nIssues the query and gets the results.\n\n|Name|Type|Description|\n|----|----|-----------|\n|None| | |\n\n|Type|Description|\n|----|-----------|\n|None| |\n\n```\nvar count = new GlideAggregate('incident');\ncount.addAggregate('COUNT');\ncount.query();\nvar incidents = 0;\nif (count.next()) {\n incidents = count.getAggregate('COUNT');\n}\ngs.info('Number of incidents: ' + incidents);\n```\n\n## Scoped GlideAggregate - setAggregateWindow\\(Number firstRow, Number lastRow\\)\n\nLimits the number of rows from the table to include in the aggregate query.\n\n|Name|Type|Description|\n|----|----|-----------|\n|firstRow|Number|Zero-based index of the first row to include in the aggregate query, inclusive.|\n|lastRow|Number|Zero-based index of the last row to include in the aggregate query, exclusive.|\n\n|Type|Description|\n|----|-----------|\n|None| |\n\nPrints the count of each category for the first ten records in the Incident \\[incident\\] table.\n\n```js\nvar incidentGroup = new GlideAggregate('incident');\nincidentGroup.addAggregate('COUNT', 'category');\nincidentGroup.setAggregateWindow(0, 10);\nincidentGroup.query();\nwhile (incidentGroup.next()) {\n var incidentCount = incidentGroup.getAggregate('COUNT', 'category');\n gs.info('{0} count: {1}', [incidentGroup.getValue('category'), incidentCount]);\n}\n```\n\nOutput:\n\n```js\ndatabase count: 1\nHardware count: 1\ninquiry count: 7\nsoftware count: 1\n```\n\n## Scoped GlideAggregate - setAggregateWorkflow\\(Boolean workflow\\)\n\nActivates or deactivates the running of business rules for aggregate queries.\n\nThe session-level workflow setting \\(`gs.getSession().setWorkflow()`\\) overrides this method. When you deactivate the session workflow, query business rules do not run regardless of this method's setting.\n\n**Warning:** Disabling the running of business rules, script engines, and audit can have a significant impact on your ServiceNow® instance and how it operates. Ensure that you thoroughly test this change before deploying it to production.\n\n\n
\n\nName\n\n\n\nType\n\n\n\nDescription\n\n
\n\nworkflow\n\n\n\nBoolean\n\n\n\nFlag that indicates whether business rules are active for the current GlideAggregate query.Valid values:\n\n- `true`: Activates business rules for the current query.\n- `false`: Deactivates business rules for the current query.\n\nDefault: `false`\n\n
|Type|Description|\n|----|-----------|\n|None| |\n\nThe following example shows how to activate business rules for an aggregate query.\n\n```\nvar incidentGA = new GlideAggregate('incident');\nincidentGA.setAggregateWorkflow(true); // activate business rules for this aggregate\nincidentGA.addAggregate('COUNT', 'sys_mod_count');\nincidentGA.query();\n```\n\nThe output includes business rule execution logs if the glide.businessrule.callstack property is in the System Properties \\[sys\\_properties\\] table and set to `true`. Set this property to `false` when not needed to avoid performance impact.\n\n```\nBUSINESS RULE - About to execute business rule 'incident query' on incident: \nBUSINESS RULE - Finished executing business rule 'incident query' on incident: \n```\n\n## Scoped GlideAggregate - setCategory\\(String category\\)\n\nSets the query category, which determines how the query is routed to a secondary database.\n\nThis method requires the Secondary Database Pools \\[com.glide.secondary\\_db\\_pools\\] plugin. Call this method to route queries to a secondary \\(read replica\\) database based on the specified category, reducing load on the primary database. Due to replication lag, execution of a query on a read replica database may return slightly out of date results compared to executing the same query on the primary database.\n\nModifications to data are always serviced by the primary database, even if setCategory\\(\\) is used in the query. For example, a call to GlideRecord.update\\(\\) is always directed to the primary database even if the GlideRecord object has been provided with the name of a secondary database category via setCategory\\(\\).\n\nFor more information, see [KB0824441 Introduction to ServiceNow Read Replica Databases](https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0824441).\n\n\n
\n\nName\n\n\n\nType\n\n\n\nDescription\n\n
\n\ncategory\n\n\n\nString\n\n\n\nName of the category to use to route the query to a secondary database.Table: Secondary Database Categories \\[sys\\_db\\_category\\]\n\n
|Type|Description|\n|----|-----------|\n|None| |\n\nThis example uses the `embedded_list` category to route the query to a secondary database.\n\n```\nvar ga = new GlideAggregate('task');\nga.setCategory('embedded_list');\nga.query();\n```\n\n## Scoped GlideAggregate - setGroup\\(Boolean b\\)\n\nSets whether to group the return results.\n\n\n
\n\nName\n\n\n\nType\n\n\n\nDescription\n\n
\n\nb\n\n\n\nBoolean\n\n\n\nFlag that indicates whether to group the results.\n\n Valid values:\n\n- true: Group the results.\n- false: Do not group the results.\n\n
|Type|Description|\n|----|-----------|\n|void| |\n\n```\nvar ga = new GlideAggregate('incident');\nga.addAggregate('COUNT', 'category');\n \nga.setGroup(true);\nga.groupBy(\"category\");\n \nga.query();\n \nwhile(ga.next()) {\n gs.info('Category ' + ga.category + ' ' + ga.getAggregate('COUNT', 'category'));\n }\n```\n\nOutput:\n\n```\nCategory database 2\nCategory hardware 7\nCategory inquiry 18\nCategory network 5\nCategory request 5\nCategory software 11\n```\n\n## Scoped GlideAggregate - setIntervalYearIncluded\\(Boolean b\\)\n\nSets whether to group results by year for day-of-week trends. These trends are created using the addTrend\\(\\) method with the **dayofweek** time interval.\n\nDependency: [Scoped GlideAggregate - addTrend\\('<fieldName>', 'dayofweek'\\)](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/api-reference/server-api-reference/c_GlideAggregateScopedAPI.md).\n\n\n
\n\nName\n\n\n\nType\n\n\n\nDescription\n\n
\n\nb\n\n\n\nBoolean\n\n\n\nFlag that indicates whether to include a year for a trend with a day-of-week time interval.Valid values:\n\n- true: Group day-of-week results by year.\n- false: Exclude the year from time interval results.\n\nDefault: true\n\n
|Type|Description|\n|----|-----------|\n|None| |\n\nThe following example shows how to count incidents created in the last six months. The incidents are separated by the day of the week, but not including the year. For example, the default results for Thursday would include the year, such as `Thursday/2023: 16`.\n\n```\nvar incidentGroup = new GlideAggregate('incident');\nincidentGroup.addEncodedQuery(\"sys_created_onRELATIVEGT@month@ago@6\");\nincidentGroup.addTrend('sys_created_on', 'dayofweek');\nincidentGroup.addAggregate('COUNT');\nincidentGroup.setIntervalYearIncluded(false);\nincidentGroup.query();\nwhile (incidentGroup.next()) {\n gs.info(incidentGroup.getValue('timeref') + ': ' + incidentGroup.getAggregate('COUNT'))};\n```\n\nOutput:\n\n```\nSunday: 1\nMonday: 15\nTuesday: 1\nWednesday: 7\nThursday: 16\nSaturday: 1\n```\n\n"}}