{"revision":"c10276e953456835cdbb1f3a60d58ae1980be353","document":{"id":999,"path":"api-reference/server-api-reference/c_GlideAggregateAPI.md","title":"GlideAggregate - Global","bundle":null,"doc_type":null,"release":"australia","body":"\n# GlideAggregate- Global\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\nTo use this API to create dynamic attributes you must have the dynamic\\_schema\\_writer role. To read dynamic data using this API you must have the dynamic\\_schema\\_reader role.\n\nSee also [Dynamic Schema](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/platform-administration/dynamic-schema.md).\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## 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 for field name. Name of the field, or path to an attribute within a dynamic attribute store, to group the results of the aggregation by.Format of the attribute path: `dyn_att_field->attr_name`\n\n- `dyn_att_field`: Name of a dynamic attribute store field on the table.\n- `attr_name`: Name of the dynamic attribute.\n\nTable: Dynamic Attribute \\[dynamic\\_attribute\\]\n\n\nSee also [Dynamic Schema](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/platform-administration/dynamic-schema.md).\n\nSee also [Dynamic Schema](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/platform-administration/dynamic-schema.md).\n\nDefault: 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\nThe following example shows how to call this method using an attribute in a dynamic attribute store.\n\n```\nvar ga_Inc = new GlideAggregate('incident');\nga_Inc.groupBy('inc_dynamic_schema->make');\nga_Inc.addAggregate('AVG', 'inc_dynamic_schema->cost');\nga_Inc.addAggregate('MIN', 'inc_dynamic_schema->cost');\nga_Inc.addAggregate('MAX', 'inc_dynamic_schema->cost');\nga_Inc.addAggregate('SUM', 'inc_dynamic_schema->cost');\nga_Inc.addAggregate('COUNT', 'inc_dynamic_schema->cost');\nga_Inc.addAggregate('STDDEV', 'inc_dynamic_schema->cost');\nga_Inc.addAggregate('GROUP_CONCAT', 'inc_dynamic_schema->cost');\nga_Inc.addAggregate('GROUP_CONCAT_DISTINCT', 'inc_dynamic_schema->cost');\nga_Inc.addAggregate('COUNT(DISTINCT', 'inc_dynamic_schema->cost');\nga_Inc.query();\nwhile (ga_Inc.next()) {\n gs.info(gr_Inc.getValue('inc_dynamic_schema->make'));\n gs.info('AVG: ' + ga_Inc.getAggregate('AVG', 'inc_dynamic_schema->cost'));\n gs.info('MAX: ' + ga_Inc.getAggregate('MAX', 'inc_dynamic_schema->cost'));\n gs.info('MIN: ' + ga_Inc.getAggregate('MIN', 'inc_dynamic_schema->cost'));\n gs.info('SUM: ' + ga_Inc.getAggregate('SUM', 'inc_dynamic_schema->cost'));\n gs.info('COUNT: ' + ga_Inc.getAggregate('COUNT', 'inc_dynamic_schema->cost'));\n gs.info('STDDEV: ' + ga_Inc.getAggregate('STDDEV', 'inc_dynamic_schema->cost'));\n gs.info('GROUP_CONCAT: ' + ga_Inc.getAggregate('GROUP_CONCAT', 'inc_dynamic_schema->cost'));\n gs.info('GROUP_CONCAT_DISTINCT: ' + ga_Inc.getAggregate('GROUP_CONCAT_DISTINCT', 'inc_dynamic_schema->cost'));\n gs.info('COUNT(DISTINCT: ' + ga_Inc.getAggregate('COUNT(DISTINCT', 'inc_dynamic_schema->cost'));\n gs.info(' ');\n}\n```\n\nOutput:\n\n```\n*** Script: BMW\n*** Script: AVG: 31852.066666666667\n*** Script: MAX: 49182\n*** Script: MIN: 16544\n*** Script: SUM: 477781\n*** Script: COUNT: 15\n*** Script: STDDEV: 10376.50218706\n*** Script: GROUP_CONCAT: 16544,18427,19083,22144,24126,27018,32353,34934,35691,35698,35799,36814,41257,48711,49182\n*** Script: GROUP_CONCAT_DISTINCT: 16544,18427,19083,22144,24126,27018,32353,34934,35691,35698,35799,36814,41257,48711,49182\n*** Script: COUNT(DISTINCT: 15\n*** Script: \n*** Script: Ford\n*** Script: AVG: 31520.090909090909\n*** Script: MAX: 47408\n*** Script: MIN: 16080\n*** Script: SUM: 346721\n*** Script: COUNT: 11\n*** Script: STDDEV: 11355.75551388\n*** Script: GROUP_CONCAT: 16080,16082,16996,27621,31662,33478,35201,36965,38085,47143,47408\n*** Script: GROUP_CONCAT_DISTINCT: 16080,16082,16996,27621,31662,33478,35201,36965,38085,47143,47408\n*** Script: COUNT(DISTINCT: 11\n*** Script: \n*** Script: Honda\n*** Script: AVG: 31972.750000000000\n*** Script: MAX: 49187\n*** Script: MIN: 15208\n*** Script: SUM: 511564\n*** Script: COUNT: 16\n*** Script: STDDEV: 10240.67632207\n*** Script: GROUP_CONCAT: 15208,17926,18365,20942,25557,28215,29090,34336,34857,34969,37144,38097,41541,41805,44325,49187\n*** Script: GROUP_CONCAT_DISTINCT: 15208,17926,18365,20942,25557,28215,29090,34336,34857,34969,37144,38097,41541,41805,44325,49187\n*** Script: COUNT(DISTINCT: 16\n*** Script: \n*** Script: Lexus\n*** Script: AVG: 29841.250000000000\n*** Script: MAX: 48406\n*** Script: MIN: 15517\n*** Script: SUM: 238730\n*** Script: COUNT: 8\n*** Script: STDDEV: 12073.56647214\n*** Script: GROUP_CONCAT: 15517,16964,22371,23900,32421,36620,42531,48406\n*** Script: GROUP_CONCAT_DISTINCT: 15517,16964,22371,23900,32421,36620,42531,48406\n*** Script: COUNT(DISTINCT: 8\n*** Script: \n*** Script: Tesla\n*** Script: AVG: 30790.000000000000\n*** Script: MAX: 45032\n*** Script: MIN: 16724\n*** Script: SUM: 431060\n*** Script: COUNT: 14\n*** Script: STDDEV: 8510.068136760580\n*** Script: GROUP_CONCAT: 16724,17173,21049,24837,28431,30594,31871,32549,32675,33778,36963,37946,41438,45032\n*** Script: GROUP_CONCAT_DISTINCT: 16724,17173,21049,24837,28431,30594,31871,32549,32675,33778,36963,37946,41438,45032\n*** Script: COUNT(DISTINCT: 14\n*** Script: \n*** Script: Toyota\n*** Script: AVG: 32115.444444444444\n*** Script: MAX: 49418\n*** Script: MIN: 15188\n*** Script: SUM: 289039\n*** Script: COUNT: 9\n*** Script: STDDEV: 12120.76776767\n*** Script: GROUP_CONCAT: 15188,16453,24596,28529,32863,34697,42790,44505,49418\n*** Script: GROUP_CONCAT_DISTINCT: 15188,16453,24596,28529,32863,34697,42790,44505,49418\n*** Script: COUNT(DISTINCT: 9\n*** Script:\n```\n\n### Scoped equivalent\n\nTo use the addAggregate\\(\\) method in a scoped application, use the corresponding scoped method: [addAggregate\\(\\)](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/api-reference/server-api-reference/c_GlideAggregateScopedAPI.md).\n\n## GlideAggregate - addBizCalendarTrend\\(String fieldName, String bizCalendarSysId\\)\n\nAdds trending by a business calendar to the aggregate query. This method allows you to pick a date and time field in the corresponding GlideRecord and group records based on a specified business calendar time span.\n\n**Note:** This method does not use the JOIN operator on the SQL queries.\n\n|Name|Type|Description|\n|----|----|-----------|\n|fieldName|String|Date and time field in the associated GlideRecord to use to determine in which group or calendar time span that the record will be included.|\n|bizCalendarSysId|String|Sys\\_id of the calendar record to use. This is the calendar that contains the desired time spans.|\n\n|Type|Description|\n|----|-----------|\n|None| |\n\nThe following code example shows the count of incident records grouped by the “Month” business calendar time spans.\n\n```\nvar monthCal = \"4d7ddda353f3001076bcddeeff7b12b1\"\nvar ga = new GlideAggregate('incident');\nga.addAggregate('COUNT');\nga.addBizCalendarTrend('opened_at', monthCal);\nga.setGroup(false);\nga.query();\ngs.print(ga.getRowCount());\nwhile (ga.next()) {\n gs.info(ga.getValue('bizcalref') + ', ' + ga.getValue('bizcalrefend') + ', ' + ga.getAggregate('COUNT'));\n}\n```\n\nOutput:\n\n```\n13\n2015-08-01 00:00:00, 2015-09-01 00:00:00, 2\n2015-11-01 00:00:00, 2015-12-01 00:00:00, 2\n2016-08-01 00:00:00, 2016-09-01 00:00:00, 3\n2016-12-01 00:00:00, 2017-01-01 00:00:00, 1\n2018-08-01 00:00:00, 2018-09-01 00:00:00, 3\n2018-09-01 00:00:00, 2018-10-01 00:00:00, 3\n2018-10-01 00:00:00, 2018-11-01 00:00:00, 2\n2019-07-01 00:00:00, 2019-08-01 00:00:00, 2\n2020-06-01 00:00:00, 2020-07-01 00:00:00, 1\n2021-01-01 00:00:00, 2021-02-01 00:00:00, 1\n2023-04-01 00:00:00, 2023-05-01 00:00:00, 15\n2023-05-01 00:00:00, 2023-06-01 00:00:00, 23\n2023-07-01 00:00:00, 2023-08-01 00:00:00, 9\n```\n\n## 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|Encoded query string to add to the aggregate.|\n\n|Type|Description|\n|----|-----------|\n|None| |\n\n```\nvar agg = new GlideAggregate('incident');\nagg.addAggregate('count','category'); \nagg.orderByAggregate('count', 'category'); \nagg.orderBy('category'); \nagg.addQuery('opened_at', '>=', 'javascript:gs.monthsAgoStart(2)'); \nagg.addQuery('opened_at', '<=', 'javascript:gs.monthsAgoEnd(2)'); \nagg.query(); \nwhile (agg.next()) { \n var category = agg.category;\n var count = agg.getAggregate('count','category');\n var query = agg.getQuery(); \n var agg2 = new GlideAggregate('incident'); \n agg2.addAggregate('count','category');\n agg2.orderByAggregate('count', 'category');\n agg2.orderBy('category');\n agg2.addQuery('opened_at', '>=', 'javascript:gs.monthsAgoStart(3)');\n agg2.addQuery('opened_at', '<=', 'javascript:gs.monthsAgoEnd(3)');\n agg2.addEncodedQuery(query);\n agg2.query();\n var last = \"\";\n while (agg2.next()) {\n last = agg2.getAggregate('count','category'); \n }\n gs.log(category + \": Last month:\" + count + \" Previous Month:\" + last);\n \n}\n```\n\n### Scoped equivalent\n\nTo use the addEncodedQuery\\(\\) method in a scoped application, use the corresponding scoped method: [addEncodedQuery\\(\\)](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/api-reference/server-api-reference/c_GlideAggregateScopedAPI.md).\n\n## GlideAggregate - addHaving\\(String name, String operator, String value\\)\n\nAdds a \"having\" element to the aggregate, such as select category, count\\(\\*\\) from incident group by category HAVING count\\(\\*\\) > 5.\n\n|Name|Type|Description|\n|----|----|-----------|\n|name|String|Aggregate to filter on. For example, COUNT.|\n|operator|String|Operator symbol. For example <, >, =, !=.|\n|value|String|Value to query on. For example, '5'.|\n\n|Type|Description|\n|----|-----------|\n|None| |\n\n```\nvar trend = new GlideAggregate('incident');\ntrend.addTrend ('opened_at','Month');\ntrend.addAggregate('COUNT');\n\n//addHaving limits the results returned to those in which the aggregate COUNT is greater than 2\ntrend.addHaving('COUNT', '>', '2');\ntrend.setGroup(false);\ntrend.query();\nwhile(trend.next()) {\n gs.print(('Incidents by month ' + trend.getValue('timeref') + ' where count is more than 2 count is: ' + trend.getAggregate('COUNT'));\n}\n```\n\nOutput:\n\n```\nIncidents by month 9/2018 where count is more than 2 count is: 3\nIncidents by month 10/2018 where count is more than 2 count is: 8\nIncidents by month 11/2018 where count is more than 2 count is: 14\n```\n\n## GlideAggregate - addHaving\\(String aggName, String fieldName, String operator, String value\\)\n\nAdds a \"having\" element to the aggregate, such as select category, count\\(\\*\\) from incident group by category HAVING count\\(\\*\\) > 5. This implementation of the method enables you to specify a specific field within a table or a dynamic attribute to act upon.\n\n\n
\n\nName\n\n\n\nType\n\n\n\nDescription\n\n
\n\naggName\n\n\n\nString\n\n\n\nAggregate to filter on. For example, COUNT.\n\n
\n\nfieldName\n\n\n\nString\n\n\n\nField name or path to an attribute within a dynamic attribute store.Format of the attribute path: `dyn_att_field->attr_name`\n\n- `dyn_att_field`: Name of a dynamic attribute store field on the table.\n- `attr_name`: Name of the dynamic attribute.\n\nTable: Dynamic Attribute \\[dynamic\\_attribute\\]\n\n\nSee also [Dynamic Schema](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/platform-administration/dynamic-schema.md).\n\nSee also [Dynamic Schema](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/platform-administration/dynamic-schema.md).\n\n
\n\noperator\n\n\n\nString\n\n\n\nOperator symbol. For example <, >, =, !=.\n\n
\n\nvalue\n\n\n\nString\n\n\n\nValue to query on. For example, '5'.\n\n
|Type|Description|\n|----|-----------|\n|None| |\n\nThe following code example shows how to identify the duplicate serial numbers within the cmdb table using a field name in the addHaving\\(\\) method call.\n\n```\nfunction getDupes(tableName, dpField) { \n var ga = new GlideAggregate(tableName);\n ga.addAggregate('COUNT', dpField); // Aggregate to count values in whatever field is passed as dpField\n ga.addHaving('COUNT', dpField, '>', '1'); // Returns only records having more than one active instance of dpField\n ga.query(); \n var arDupes = new Array(); // Build array to push the results into\n while (ga.next()) { \n arDupes.push(ga.getValue(dpField)); // Push the value of the dupe field to the array \n }\n return arDupes;\n}\n \nvar tableName = \"cmdb\";\nvar dpField = \"serial_number\";\n \ngs.print(getDupes(tableName, dpField));\n```\n\nOutput:\n\n```\nIncidents by month 9/2018 where count is more than 2 count is: 3\nIncidents by month 10/2018 where count is more than 2 count is: 8\nIncidents by month 11/2018 where count is more than 2 count is: 14\n```\n\nThe following example shows to identify duplicates, but uses a dynamic attribute instead of a field in the addHaving\\(\\) method call.\n\n```\nfunction getDupes(tableName, dpField) { \n var ga = new GlideAggregate(tablename);\n ga.addAggregate('COUNT', dpField); // Aggregate to count values in whatever dynamic attribute is passed as dpField\n ga.addHaving('COUNT', dpField, '>', '1'); // Returns only records having more than one active instance of dpField\n ga.query(); \n var arDupes = new Array(); // Build array to push the results into\n while (ga.next()) { \n arDupes.push(ga.getValue(dpField)); // Push the value of the dupe field to the array \n }\n return arDupes;\n}\n \nvar tableName = \"cmdb\";\nvar dpField = \"dyn_att_field->attr\";\n \ngs.print(getDupes(tableName, dpField));\n```\n\nOutput:\n\n```\nIncidents by month 9/2018 where count is more than 2 count is: 3\nIncidents by month 10/2018 where count is more than 2 count is: 8\nIncidents by month 11/2018 where count is more than 2 count is: 14\n```\n\n## GlideAggregate - addTrend\\(String fieldName, String timeInterval, Number numUnits\\)\n\nAdds a trend for a 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 [GlideAggregate - setIntervalYearIncluded\\(Boolean b\\)](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/api-reference/server-api-reference/c_GlideAggregateAPI.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|None| |\n\n```\nvar trend = new GlideAggregate('incident'); \ntrend.addTrend ('opened_at','month'); \ntrend.addAggregate('COUNT'); \ntrend.setGroup(false); \ntrend.query(); \nwhile(trend.next()) { \n gs.print(trend.getValue('timeref') + ': ' + trend.getAggregate('COUNT')); \n} \n```\n\nOutput:\n\n```\n9/2018: 3\n10/2018: 8\n11/2018: 14\n```\n\n### Scoped equivalent\n\nTo use the addTrend\\(\\) method in a scoped application, use the corresponding scoped method: [addTrend\\(\\)](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/api-reference/server-api-reference/c_GlideAggregateScopedAPI.md).\n\n## GlideAggregate - getAggregate\\(String agg, String name\\)\n\nGets 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, or path to an attribute within a dynamic schema, to get the aggregate from.Format of the attribute path: `dyn_att_field->attr_name`\n\n- `dyn_att_field`: Name of a dynamic attribute store field on the table.\n- `attr_name`: Name of the dynamic attribute.\n\nTable: Dynamic Attribute \\[dynamic\\_attribute\\]\n\n\nSee also [Dynamic Schema](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/platform-administration/dynamic-schema.md).\n\nSee also [Dynamic Schema](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/platform-administration/dynamic-schema.md).\n\n
\n
\n\nType\n\n\n\nDescription\n\n
\n\nString\n\n\n\nValue 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
This example shows how to obtain the COUNT aggregate.\n\n```\nfunction doMyBusinessRule(assigned_to, number) {\n var agg = new GlideAggregate('incident');\n agg.addQuery('assigned_to', assigned_to);\n agg.addQuery('category', number);\n agg.addAggregate(\"COUNT\");\n agg.query();\n var answer = 'false';\n if (agg.next()) {\n answer = agg.getAggregate(\"COUNT\");\n if (answer > 0)\n answer = 'true';\n else\n answer = 'false';\n }\n return answer; \n}\n```\n\nThis example shows 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\nThe following example shows how to call this method using an attribute in a dynamic attribute store.\n\n```\nvar ga_Inc = new GlideAggregate('incident');\nga_Inc.groupBy('inc_dynamic_schema->make');\nga_Inc.addAggregate('AVG', 'inc_dynamic_schema->cost');\nga_Inc.addAggregate('MIN', 'inc_dynamic_schema->cost');\nga_Inc.addAggregate('MAX', 'inc_dynamic_schema->cost');\nga_Inc.addAggregate('SUM', 'inc_dynamic_schema->cost');\nga_Inc.addAggregate('COUNT', 'inc_dynamic_schema->cost');\nga_Inc.addAggregate('STDDEV', 'inc_dynamic_schema->cost');\nga_Inc.addAggregate('GROUP_CONCAT', 'inc_dynamic_schema->cost');\nga_Inc.addAggregate('GROUP_CONCAT_DISTINCT', 'inc_dynamic_schema->cost');\nga_Inc.addAggregate('COUNT(DISTINCT', 'inc_dynamic_schema->cost');\nga_Inc.query();\nwhile (ga_Inc.next()) {\n gs.info(gr_Inc.getValue('inc_dynamic_schema->make'));\n gs.info('AVG: ' + ga_Inc.getAggregate('AVG', 'inc_dynamic_schema->cost'));\n gs.info('MAX: ' + ga_Inc.getAggregate('MAX', 'inc_dynamic_schema->cost'));\n gs.info('MIN: ' + ga_Inc.getAggregate('MIN', 'inc_dynamic_schema->cost'));\n gs.info('SUM: ' + ga_Inc.getAggregate('SUM', 'inc_dynamic_schema->cost'));\n gs.info('COUNT: ' + ga_Inc.getAggregate('COUNT', 'inc_dynamic_schema->cost'));\n gs.info('STDDEV: ' + ga_Inc.getAggregate('STDDEV', 'inc_dynamic_schema->cost'));\n gs.info('GROUP_CONCAT: ' + ga_Inc.getAggregate('GROUP_CONCAT', 'inc_dynamic_schema->cost'));\n gs.info('GROUP_CONCAT_DISTINCT: ' + ga_Inc.getAggregate('GROUP_CONCAT_DISTINCT', 'inc_dynamic_schema->cost'));\n gs.info('COUNT(DISTINCT: ' + ga_Inc.getAggregate('COUNT(DISTINCT', 'inc_dynamic_schema->cost'));\n gs.info(' ');\n}\n```\n\nOutput:\n\n```\n*** Script: BMW\n*** Script: AVG: 31852.066666666667\n*** Script: MAX: 49182\n*** Script: MIN: 16544\n*** Script: SUM: 477781\n*** Script: COUNT: 15\n*** Script: STDDEV: 10376.50218706\n*** Script: GROUP_CONCAT: 16544,18427,19083,22144,24126,27018,32353,34934,35691,35698,35799,36814,41257,48711,49182\n*** Script: GROUP_CONCAT_DISTINCT: 16544,18427,19083,22144,24126,27018,32353,34934,35691,35698,35799,36814,41257,48711,49182\n*** Script: COUNT(DISTINCT: 15\n*** Script: \n*** Script: Ford\n*** Script: AVG: 31520.090909090909\n*** Script: MAX: 47408\n*** Script: MIN: 16080\n*** Script: SUM: 346721\n*** Script: COUNT: 11\n*** Script: STDDEV: 11355.75551388\n*** Script: GROUP_CONCAT: 16080,16082,16996,27621,31662,33478,35201,36965,38085,47143,47408\n*** Script: GROUP_CONCAT_DISTINCT: 16080,16082,16996,27621,31662,33478,35201,36965,38085,47143,47408\n*** Script: COUNT(DISTINCT: 11\n*** Script: \n*** Script: Honda\n*** Script: AVG: 31972.750000000000\n*** Script: MAX: 49187\n*** Script: MIN: 15208\n*** Script: SUM: 511564\n*** Script: COUNT: 16\n*** Script: STDDEV: 10240.67632207\n*** Script: GROUP_CONCAT: 15208,17926,18365,20942,25557,28215,29090,34336,34857,34969,37144,38097,41541,41805,44325,49187\n*** Script: GROUP_CONCAT_DISTINCT: 15208,17926,18365,20942,25557,28215,29090,34336,34857,34969,37144,38097,41541,41805,44325,49187\n*** Script: COUNT(DISTINCT: 16\n*** Script: \n*** Script: Lexus\n*** Script: AVG: 29841.250000000000\n*** Script: MAX: 48406\n*** Script: MIN: 15517\n*** Script: SUM: 238730\n*** Script: COUNT: 8\n*** Script: STDDEV: 12073.56647214\n*** Script: GROUP_CONCAT: 15517,16964,22371,23900,32421,36620,42531,48406\n*** Script: GROUP_CONCAT_DISTINCT: 15517,16964,22371,23900,32421,36620,42531,48406\n*** Script: COUNT(DISTINCT: 8\n*** Script: \n*** Script: Tesla\n*** Script: AVG: 30790.000000000000\n*** Script: MAX: 45032\n*** Script: MIN: 16724\n*** Script: SUM: 431060\n*** Script: COUNT: 14\n*** Script: STDDEV: 8510.068136760580\n*** Script: GROUP_CONCAT: 16724,17173,21049,24837,28431,30594,31871,32549,32675,33778,36963,37946,41438,45032\n*** Script: GROUP_CONCAT_DISTINCT: 16724,17173,21049,24837,28431,30594,31871,32549,32675,33778,36963,37946,41438,45032\n*** Script: COUNT(DISTINCT: 14\n*** Script: \n*** Script: Toyota\n*** Script: AVG: 32115.444444444444\n*** Script: MAX: 49418\n*** Script: MIN: 15188\n*** Script: SUM: 289039\n*** Script: COUNT: 9\n*** Script: STDDEV: 12120.76776767\n*** Script: GROUP_CONCAT: 15188,16453,24596,28529,32863,34697,42790,44505,49418\n*** Script: GROUP_CONCAT_DISTINCT: 15188,16453,24596,28529,32863,34697,42790,44505,49418\n*** Script: COUNT(DISTINCT: 9\n*** Script:\n```\n\n### Scoped equivalent\n\nTo use the getAggregate\\(\\) method in a scoped application, use the corresponding scoped method: [getAggregate\\(\\)](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/api-reference/server-api-reference/c_GlideAggregateScopedAPI.md).\n\n## GlideAggregate - getDynamicAttributeValue\\(String fullPath\\)\n\nReturns the value of the dynamic attribute located at a specified path.\n\n\n
\n\nName\n\n\n\nType\n\n\n\nDescription\n\n
\n\nfullPath\n\n\n\nString\n\n\n\nPath to use to locate the desired dynamic attribute. Format of the attribute path: `dyn_att_field->attr_name`\n\n- `dyn_att_field`: Name of a dynamic attribute store field on the table.\n- `attr_name`: Name of the dynamic attribute.\n\nTable: Dynamic Attribute \\[dynamic\\_attribute\\]\n\n\nSee also [Dynamic Schema](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/platform-administration/dynamic-schema.md).\n\n
\n
\n\nType\n\n\n\nDescription\n\n
\n\nObject\n\n\n\nValue of the dynamic attribute located at the specified path.If the **fullPath** parameter contains invalid data or the specified attribute value isn't one of the supported data types, returns null.\n\n
The following code example shows how to call this method.\n\n```\nvar ga_Tab = new GlideAggregate('u_mytable');\nga_Tab.groupBy('u_dyn_attr_store->color');\nga_Tab.addAggregate('AVG', 'u_dyn_attr_store->number');\nga_Tab.query();\n\nwhile(ga_Tab.next()) {\n gs.info(\n \"color: \" + ga_Tab.getDynamicAttributeValue('u_dyn_attr_store->color')\n + \" avg: \" + ga_Tab.getAggregate('AVG', 'u_dyn_attr_store->number'));\n}\n```\n\nOutput:\n\n```\n*** Script: color: blue avg: 5.0000\n*** Script: color: red avg: 8.0000\n*** Script: color: yellow avg: 7.0000\n```\n\n## GlideAggregate - getDynamicAttributeValue\\(String dynamicAttributeField, String attrPath\\)\n\nReturns the value of the dynamic attribute located at a specified field in the current table and a specified attribute path.\n\n\n
\n\nName\n\n\n\nType\n\n\n\nDescription\n\n
\n\ndynamicAttributeField\n\n\n\nString\n\n\n\nName of the field in the table that contains the dynamic attribute.\n\n
\n\nattrPath\n\n\n\nString\n\n\n\nAttribute path to use to locate the associated dynamic schema attribute.Format: `\"attr_name\"`\n\n`attr_name`: Name of the dynamic attribute.\n\nTable: In the attribute field of the Dynamic Attribute \\[dynamic\\_attribute\\] table.\n\n
\n
\n\nType\n\n\n\nDescription\n\n
\n\nObject\n\n\n\nValue of the dynamic attribute located at the specified path.If either the **dynamicAttributeField** or **attrPath** parameters are invalid, returns null.\n\n
The following code example shows how to call this method.\n\n```\nvar ga_Tab = new GlideAggregate('u_mytable');\nga_Tab.groupBy('u_dyn_attr_store->color');\nga_Tab.addAggregate('AVG', 'u_dyn_attr_store->number');\nga_Tab.query();\n\nwhile(ga_Tab.next()) {\n gs.info(\n \"color: \" + ga_Tab.getDynamicAttributeValue('u_dyn_attr_store', 'color')\n + \" avg: \" + ga_Tab.getAggregate('AVG', 'u_dyn_attr_store->number'));\n}\n```\n\nOutput:\n\n```\n*** Script: color: blue avg: 5.0000\n*** Script: color: red avg: 8.0000\n*** Script: color: yellow avg: 7.0000\n```\n\n## GlideAggregate - getDynamicAttributeDisplayValue\\(String fullPath\\)\n\nReturns the display value of the dynamic attribute located at the specified path.\n\n\n
\n\nName\n\n\n\nType\n\n\n\nDescription\n\n
\n\nfullPath\n\n\n\nString\n\n\n\nPath to use to locate the desired dynamic attribute. Format of the attribute path: `dyn_att_field->attr_name`\n\n- `dyn_att_field`: Name of a dynamic attribute store field on the table.\n- `attr_name`: Name of the dynamic attribute.\n\nTable: Dynamic Attribute \\[dynamic\\_attribute\\]\n\n\nSee also [Dynamic Schema](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/platform-administration/dynamic-schema.md).\n\n
\n
\n\nType\n\n\n\nDescription\n\n
\n\nString\n\n\n\nDisplay value of the dynamic attribute located at the specified path.If the **fullPath** parameter is invalid, returns null.\n\n
The following code example shows how to call this method.\n\n```\nvar ga_Tab = new GlideAggregate('u_mytable');\nga_Tab.groupBy('u_dyn_attr_store->color');\nga_Tab.addAggregate('AVG', 'u_dyn_attr_store->number');\nga_Tab.query();\n\nwhile(ga_Tab.next()) {\n gs.info(\n \"color: \" + ga_Tab.getDynamicAttributeDisplayValue('u_dyn_attr_store->color')\n + \" avg: \" + ga_Tab.getAggregate('AVG', 'u_dyn_attr_store->number'));\n}\n```\n\nOutput:\n\n```\n*** Script: color: blue avg: 5.0000\n*** Script: color: red avg: 8.0000\n*** Script: color: yellow avg: 7.0000\n```\n\n## GlideAggregate - getDynamicAttributeDisplayValue\\(String dynamicAttributeField, String attrPath\\)\n\nReturns the display value of the dynamic attribute located in a specified table field and attribute path.\n\n\n
\n\nName\n\n\n\nType\n\n\n\nDescription\n\n
\n\ndynamicAttributeField\n\n\n\nString\n\n\n\nName of the field in the table that contains the dynamic attribute.\n\n
\n\nattrPath\n\n\n\nString\n\n\n\nAttribute path to use to locate the associated dynamic schema attribute.Format: `\"attr_name\"`\n\n`attr_name`: Name of the dynamic attribute.\n\nTable: In the attribute field of the Dynamic Attribute \\[dynamic\\_attribute\\] table.\n\n
\n
\n\nType\n\n\n\nDescription\n\n
\n\nString\n\n\n\nDisplay value of the dynamic attribute located at the specified path.If either the **dynamicAttributeField** or **attrPath** parameters are invalid, returns null.\n\n
The following code example shows how to call this method.\n\n```\nvar ga_Tab = new GlideAggregate('u_mytable');\nga_Tab.groupBy('u_dyn_attr_store->color');\nga_Tab.addAggregate('AVG', 'u_dyn_attr_store->number');\nga_Tab.query();\n\nwhile(ga_Tab.next()) {\n gs.info(\n \"color: \" + ga_Tab.getDynamicAttributeDisplayValue('u_dyn_attr_store', 'color')\n + \" avg: \" + ga_Tab.getAggregate('AVG', 'u_dyn_attr_store->number'));\n}\n```\n\nOutput:\n\n```\n*** Script: color: blue avg: 5.0000\n*** Script: color: red avg: 8.0000\n*** Script: color: yellow avg: 7.0000\n```\n\n## GlideAggregate - getQuery\\(\\)\n\nRetrieves the query necessary to return the current aggregate.\n\n|Name|Type|Description|\n|----|----|-----------|\n|None| | |\n\n|Type|Description|\n|----|-----------|\n|String|The query.|\n\n```\nvar agg = new GlideAggregate('incident');\nagg.addAggregate('count','category'); \nagg.orderByAggregate('count', 'category'); \nagg.orderBy('category'); \nagg.addQuery('opened_at', '>=', 'javascript:gs.monthsAgoStart(2)'); \nagg.addQuery('opened_at', '<=', 'javascript:gs.monthsAgoEnd(2)'); \nagg.query(); \nwhile (agg.next()) { \n var category = agg.category;\n var count = agg.getAggregate('count','category');\n var query = agg.getQuery(); \n var agg2 = new GlideAggregate('incident'); \n agg2.addAggregate('count','category');\n agg2.orderByAggregate('count', 'category');\n agg2.orderBy('category');\n agg2.addQuery('opened_at', '>=', 'javascript:gs.monthsAgoStart(3)');\n agg2.addQuery('opened_at', '<=', 'javascript:gs.monthsAgoEnd(3)');\n agg2.addEncodedQuery(query);\n agg2.query();\n var last = \"\";\n while (agg2.next()) {\n last = agg2.getAggregate('count','category'); \n }\n gs.log(category + \": Last month:\" + count + \" Previous Month:\" + last);\n \n}\n```\n\n## 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 equivalent\n\nTo use the getRowCount\\(\\) method in a scoped application, use the corresponding scoped method: [Scoped GlideAggregate - getRowCount\\(\\)](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/api-reference/server-api-reference/c_GlideAggregateScopedAPI.md).\n\n## GlideAggregate - getTotal\\(String agg, String name\\)\n\nReturns the number of records by summing an aggregate.\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\nName of the field to aggregate.\n\n
|Type|Description|\n|----|-----------|\n|Number|Number of records.|\n\n```\nvar incidentGA = new GlideAggregate('incident');\nincidentGA.addQuery('category', 'software'); \nincidentGA.addAggregate('COUNT'); \nincidentGA.addTrend('opened_at','year'); // Counting number of incidents for software category per year\nincidentGA.setGroup(false);\nincidentGA.query();\nwhile(incidentGA.next()){\n gs.info('Incidents opened on year - '+incidentGA.getValue('timeref')+' - '+incidentGA.getAggregate('COUNT'));\n}\ngs.info('Total Aggregate Value >> '+ incidentGA.getTotal('COUNT')); \n```\n\nOutput:\n\n```\nIncidents opened on year - 2015 - 1\nIncidents opened on year - 2018 - 5\nIncidents opened on year - 2020 - 10\nTotal Aggregate Value >> 16\n```\n\n## GlideAggregate - getValue\\(String name\\)\n\nReturns the value of a field or a dynamic attribute.\n\n\n
\n\nName\n\n\n\nType\n\n\n\nDescription\n\n
\n\nname\n\n\n\nString\n\n\n\nField name or path to an attribute within a dynamic attribute store.Format of the attribute path: `dyn_att_field->attr_name`\n\n- `dyn_att_field`: Name of a dynamic attribute store field on the table.\n- `attr_name`: Name of the dynamic attribute.\n\nTable: Dynamic Attribute \\[dynamic\\_attribute\\]\n\n\nSee also [Dynamic Schema](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/platform-administration/dynamic-schema.md).\n\n
|Type|Description|\n|----|-----------|\n|String|Value of the specified field. Returns null if invalid \\(not part of result set\\).|\n\n```\nvar trend = new GlideAggregate('incident');\ntrend.addTrend ('opened_at','Month');\ntrend.addAggregate('COUNT');\n\n//addHaving limits the results returned to those in which the aggregate COUNT is greater than 2\ntrend.addHaving('COUNT', '>', '2');\ntrend.setGroup(false);\ntrend.query();\nwhile(trend.next()) {\n gs.print(('Incidents by month ' + trend.getValue('timeref') + ' where count is more than 2 count is: ' + trend.getAggregate('COUNT'));\n}\n```\n\nOutput:\n\n```\nIncidents by month 9/2018 where count is more than 2 count is: 3\nIncidents by month 10/2018 where count is more than 2 count is: 8\nIncidents by month 11/2018 where count is more than 2 count is: 14\n```\n\n### Scoped equivalent\n\nTo use the getValue\\(\\) method in a scoped application, use the corresponding scoped method: [getValue\\(\\)](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/api-reference/server-api-reference/c_GlideAggregateScopedAPI.md).\n\n## GlideAggregate - groupBy\\(String name\\)\n\nProvides the name of a field, or an attribute within a dynamic attribute store, to use when grouping the aggregates.\n\nMay be called numerous times to set multiple group fields.\n\n\n
\n\nName\n\n\n\nType\n\n\n\nDescription\n\n
\n\nname\n\n\n\nString\n\n\n\nField name or path to an attribute within a dynamic attribute store.Format of the attribute path: `dyn_att_field->attr_name`\n\n- `dyn_att_field`: Name of a dynamic attribute store field on the table.\n- `attr_name`: Name of the dynamic attribute.\n\nTable: Dynamic Attribute \\[dynamic\\_attribute\\]\n\n\nSee also [Dynamic Schema](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/platform-administration/dynamic-schema.md).\n\nSee also [Dynamic Schema](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/platform-administration/dynamic-schema.md).\n\n
|Type|Description|\n|----|-----------|\n|None| |\n\nThe following example shows how to call this method.\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.log(category + \" Update counts: MIN = \" + min + \" MAX = \" + max + \" AVG = \" + avg);\n}\n```\n\nThe following example shows how to call this method using an attribute in a dynamic attribute store.\n\n```\nvar ga_AppTab = new GlideAggregate('application_table');\nga_AppTab.addGroupBy('dyn_att_field->attr');\nga_AppTab.addAggregate('AVG', 'dyn_att_field->attr1');\nga_AppTab.addHaving('SUM', 'dyn_att_field->attr2', '>=', 420);\nga.query();\n```\n\n### Scoped equivalent\n\nTo use the groupBy\\(\\) method in a scoped application, use the corresponding scoped method: [groupBy\\(\\)](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/api-reference/server-api-reference/c_GlideAggregateScopedAPI.md).\n\n## GlideAggregate - orderBy\\(String name\\)\n\nOrders the aggregates using the value of the specified field, dynamic attribute path, or glidefunction. 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\nField name, path to an attribute within a dynamic attribute store, or glidefunction to use to order the aggregates.Format of the attribute path: `dyn_att_field->attr_name`\n\n- `dyn_att_field`: Name of a dynamic attribute store field on the table.\n- `attr_name`: Name of the dynamic attribute.\n\nTable: Dynamic Attribute \\[dynamic\\_attribute\\]\n\n\nSee also [Dynamic Schema](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/platform-administration/dynamic-schema.md).\n\nSee also [Dynamic Schema](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/platform-administration/dynamic-schema.md).\n\nglidefunction format: `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.orderByAggregate('count', 'category'); \nagg.orderBy('category'); \nagg.addQuery('opened_at', '>=', 'javascript:gs.monthsAgoStart(2)'); \nagg.addQuery('opened_at', '<=', 'javascript:gs.monthsAgoEnd(2)'); \nagg.query(); \nwhile (agg.next()) { \n var category = agg.category;\n var count = agg.getAggregate('count','category');\n var query = agg.getQuery(); \n var agg2 = new GlideAggregate('incident'); \n agg2.addAggregate('count','category');\n agg2.orderByAggregate('count', 'category');\n agg2.orderBy('category');\n agg2.addQuery('opened_at', '>=', 'javascript:gs.monthsAgoStart(3)');\n agg2.addQuery('opened_at', '<=', 'javascript:gs.monthsAgoEnd(3)');\n agg2.addEncodedQuery(query);\n agg2.query();\n var last = \"\";\n while (agg2.next()) {\n last = agg2.getAggregate('count','category'); \n }\n gs.log(category + \": Last month:\" + count + \" Previous Month:\" + last);\n}\n```\n\n### Scoped equivalent\n\nTo use the orderBy\\(\\) method in a scoped application, use the corresponding scoped method: [orderBy\\(\\)](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/api-reference/server-api-reference/c_GlideAggregateScopedAPI.md).\n\n## GlideAggregate - orderByAggregate\\(String agg, String fieldName\\)\n\nOrders the aggregates based on the specified aggregate and field or dynamic attribute.\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 aggregation.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\nfieldName\n\n\n\nString\n\n\n\nName of the field or path to an attribute within a dynamic attribute store to aggregate.Format of the attribute path: `dyn_att_field->attr_name`\n\n- `dyn_att_field`: Name of a dynamic attribute store field on the table.\n- `attr_name`: Name of the dynamic attribute.\n\nTable: Dynamic Attribute \\[dynamic\\_attribute\\]\n\n\nSee also [Dynamic Schema](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/platform-administration/dynamic-schema.md).\n\nSee also [Dynamic Schema](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/platform-administration/dynamic-schema.md).\n\n
|Type|Description|\n|----|-----------|\n|None| |\n\n```\nvar agg = new GlideAggregate('incident');\nagg.addAggregate('count','category'); \nagg.orderByAggregate('count', 'category'); \nagg.orderBy('category'); \nagg.addQuery('opened_at', '>=', 'javascript:gs.monthsAgoStart(2)'); \nagg.addQuery('opened_at', '<=', 'javascript:gs.monthsAgoEnd(2)'); \nagg.query(); \nwhile (agg.next()) { \n var category = agg.category;\n var count = agg.getAggregate('count','category');\n var query = agg.getQuery(); \n var agg2 = new GlideAggregate('incident'); \n agg2.addAggregate('count','category');\n agg2.orderByAggregate('count', 'category');\n agg2.orderBy('category');\n agg2.addQuery('opened_at', '>=', 'javascript:gs.monthsAgoStart(3)');\n agg2.addQuery('opened_at', '<=', 'javascript:gs.monthsAgoEnd(3)');\n agg2.addEncodedQuery(query);\n agg2.query();\n var last = \"\";\n while (agg2.next()) {\n last = agg2.getAggregate('count','category'); \n }\n gs.log(category + \": Last month:\" + count + \" Previous Month:\" + last);\n \n}\n```\n\n### Scoped equivalent\n\nTo use the orderByAggregate\\(\\) method in a scoped application, use the corresponding scoped method: [orderByAggregate\\(\\)](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/api-reference/server-api-reference/c_GlideAggregateScopedAPI.md).\n\n## 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 agg = new GlideAggregate('incident');\nagg.addAggregate('count','category'); \nagg.orderByAggregate('count', 'category'); \nagg.orderBy('category'); \nagg.addQuery('opened_at', '>=', 'javascript:gs.monthsAgoStart(2)'); \nagg.addQuery('opened_at', '<=', 'javascript:gs.monthsAgoEnd(2)'); \nagg.query(); \nwhile (agg.next()) { \n var category = agg.category;\n var count = agg.getAggregate('count','category');\n var query = agg.getQuery(); \n var agg2 = new GlideAggregate('incident'); \n agg2.addAggregate('count','category');\n agg2.orderByAggregate('count', 'category');\n agg2.orderBy('category');\n agg2.addQuery('opened_at', '>=', 'javascript:gs.monthsAgoStart(3)');\n agg2.addQuery('opened_at', '<=', 'javascript:gs.monthsAgoEnd(3)');\n agg2.addEncodedQuery(query);\n agg2.query();\n var last = \"\";\n while (agg2.next()) {\n last = agg2.getAggregate('count','category'); \n }\n gs.log(category + \": Last month:\" + count + \" Previous Month:\" + last);\n```\n\n### Scoped equivalent\n\nTo use the query\\(\\) method in a scoped application, use the corresponding scoped method: [query\\(\\)](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/api-reference/server-api-reference/c_GlideAggregateScopedAPI.md).\n\n## 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 equivalent\n\nTo use the setAggregateWindow\\(\\) method in a scoped application, use the corresponding scoped method: [setAggregateWindow\\(\\)](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/api-reference/server-api-reference/c_GlideAggregateScopedAPI.md).\n\n## 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## 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 equivalent\n\nTo use the setCategory\\(\\) method in a scoped application, use the corresponding scoped method: [setCategory\\(\\)](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/api-reference/server-api-reference/c_GlideAggregateScopedAPI.md).\n\n## GlideAggregate - setGroup\\(Boolean b\\)\n\nSets whether to group the 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.Valid values:\n\n- true: Group the results.\n- false: Do not group the results.\n\n
|Type|Description|\n|----|-----------|\n|None| |\n\n```\nvar ga = new GlideAggregate('incident');\nga.addAggregate('COUNT', 'category');\n \nga.setGroup(true);\n```\n\n### Scoped equivalent\n\nTo use the setGroup\\(\\) method in a scoped application, use the corresponding scoped method: [setGroup\\(\\)](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/api-reference/server-api-reference/c_GlideAggregateScopedAPI.md).\n\n## 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: [GlideAggregate - addTrend\\('<fieldName>', 'dayofweek'\\)](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/api-reference/server-api-reference/c_GlideAggregateAPI.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### Scoped equivalent\n\nTo use the setIntervalYearIncluded\\(\\) method in a scoped application, use the corresponding scoped method: [setIntervalYearIncluded\\(\\)](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/api-reference/server-api-reference/c_GlideAggregateScopedAPI.md).\n\n"}}