Hello friends welcome back to my YouTube channel basico service not learning so in today's video we are going to talk about one of the servicenow API that is Glide aggregate in this video we will discuss all the great aggregate functions in depth by the help of use cases and at the end of this video you will be having a good understanding of all the Glide aggregate functions their aggregation capabilities in detail so let's see what is Glide aggregate first so Glide aggregate is an extension of Glide record and basically its provide and capability to do aggregation like min max count sum average Etc and this API is mostly used for calculation perspective this is only a definition right so to understand the aggregation capabilities which is mentioned over here let's say min max count sum average group concat group concat listing STD Dev capabilities in depth let's move to the use cases so here I have created four use cases the first use case is that find out the count of active incident the Second Use case is find out how many number of incident belongs to each category or group by category I will also showcase you in the list view that what basically we want to achieve okay the third use case is use of some average Min and Max in cmdbci table this is basically to explore the aggregation capabilities of Glide aggregate to understand in depth that how exactly we can use it and how exactly it is displaying the results the fourth one is display all the distinct classes referred in cmdp underscore CI table record we know that each and every record in cmdp underscore CI table belongs to a specific class right so what we have to do we have to figure out all the classes in a single string which is comma separated for this also we are going to use one of our Glide aggregate aggregation capability so let's implement the use case and see how exactly we can use glad aggregate API in service now while doing scripting so friends for the same basically what I have done is that I have opened my personal development instance in two of the browser okay so in one of the browser I will be writing the script and another browser I will be displaying you the records of service now let's say incident same DB underscore CI table right so for scripting basically today I am using a background script I'm opening the same in another tab see so I have this kind of UI if you don't have this type of UI it means you have not installed SN uterus tool so you have to install the same it makes the editor very interactive and user friendly to write the code and in another browser I have this where I am opening an incident list okay so here in incident for all basically we have 67 incident available in the list view of incident so as per the use case the first use case says that we have to find out the count of active incident okay so let's move to the data let's start coding the light and sorry let aggregate okay just for understanding I have just mentioned over here so the first use case says that we have to find out the number of incident okay so before starting the Glide aggregate I will also tell you that how we can find out the number or say total count of incident in Glide record because in Glide record also we can find out the total count of a particular record displaying in a list okay so for that what we basically we use that where count new light record I am giving you examples so that at least we have understanding that how we are getting the same thing in Glide Aggregate and why it is recommended to try the same thing in Glide aggregate so in Glide record basically what we do first we create a particular object of a Glide record class then count dot query and then count dot get row count so this get row count is basically giving us a count of a particular record which is available after performing a query okay so here we have get row count let's say GS dot info so it is displaying me 67 because which we have seen over here right 67 but all these incidents which is displayed over here we are not talking about it is active or not right so what for that what we have to do we have to apply a filter in a particular code so here we can apply a filter that is Count Dot query and we can say so now it is displaying me count 40 so go here say filter and type active okay is true run so the number of record displaying over here is 40. so through Glide record basically we can achieve the same in terms of count let's say if we made a count of a record displaying after applying some filter and all right but how we can use the same in Glide aggregate okay so let's see how we can use that let me rename it count so it make a bit sense of it so the same way where count new light Aggregate and then the same we did for blood record we have to give the table name incident okay so here in Glide aggregate basically we have different functions available which we can use to find out the thing so one of the function is ADD aggregate so let's check down and aggregate so ad aggregate is basically need some parameters so the first parameter will be the Glide aggregation which we want so what we want we want count right so we have to give count over here then we can say count dot very the same we did for Glide drop card right while dot next and here we can type GS dot info so now to print the things what we have to do we we have another function in Glad aggregate that is get aggregate so aggregate and the same what we have given in the parameter same we have to give in the get aggregate and here yes we are done with the code run script see it is displaying me as 67. right 67 number of Records but here we also have not applied the particular filter right where we are saying that active is true so we can apply the filter over here count Dot add query it is almost same as Glide record but the thing is that we have some different functions available let's say add Aggregate and get aggregate these two functions are most important functions of light aggregate where we are user aggregations right so where we can pass parameters also but we have to make sure whatever parameters we are passing in add aggregate the same parameters we have to pass in get aggregate okay so the ad query what we have to say we have to say that active is so the count is 40. so as we have seen here so here the total count is 40 right so in this way England aggregate basically we can use this count aggregation to find out the total number of rows available okay so let's move to a second use case now so the Second Use case says that find out how many number of incident belongs to each category or group by category so here are two things right count how many numbers so both are saying basically count and talking about numbers only right so here we also have to find out that how many incident belongs to each category so let's navigate to service my application first see here we have incident where we have active two let's make it all so here we have 67 incident which belongs to different different categories right so let's Group by by category I can say Group by by category so once we Group by category it is showing me that this empty category is having four incident this database category having two incident this Hardware category have 10 seconds so let's say in any of the scenario where we have to to find out that what all categories contains how many numbers of problems and change it depends on the particular table right that depends on the requirement but the logic will be same right so how to implement the same because logic will be same everywhere so let's move to section so let me change the name how many number of incident incident each category okay so here we are talking about incident so the first syntax will be same where count New Blood aggregate incident right we are not talking about active equals to so let's move it now here we also need number of incident right so it means we have to use the aggregation that is count okay the rest of the code is find query next and when we use add aggregate then we can use the get aggregate also right so to group Pi by category we can use count dot Group by okay and here we can do PI by syntax is aggregate by count then Group by by category query the particular table if the records are available there then get aggregate run script see it is displaying me 4 to 10 33 513 so it is 4 to 10 33 513 so it is basically displaying me account but make it more informative right so what we can do here is that we can print quickly also that play value category let's say Plus then here okay so what so what I have done here is that through this particular syntax printing the category also right let's transcript see here now it is displaying me empty category has four database has two Hardware has 10 inquiry 33 Network 5 software 13 the same displayed over here right so this is a very simple four line code right which is giving us a complete information so in this way the Glad aggregate is helpful for us right by using this we can find out the aggregation in a very simpler way okay so now let's we don't need this code how we can use the same in add aggregate let me comment this it means we are not group buying the Same by category now here we can also pass second parameter that is category okay so what I am saying over here is that count per category means filter the things through category first and then count it okay as I said whatever we are giving in add aggregate we have to give the same thing in the get Aggregates but here we have not given the same right let's run the script see it is displaying me null null okay so what we have to do we have to copy the same thing over here right see here so instead of this what I have done I have pass one more parameter that is a field name here we have aggregation here we had a have a field name so accordingly it is displaying me number of categories and the count of incident okay so in this way we have implemented the Second Use case which has given us the understanding of two functions that is ADD Aggregate and the get aggregate even though it also have given us the understanding of one of the Glad aggregate aggregation that is Count now move to the third use case so the third use case says that we have to use of some average Min and Max aggregation in cmdp underscore CI table so let's move to a service for instance first and open cmdb underscore CI table okay so this particular table cmdb underscore CI has 2784 results here we have cost okay so whatever scenario we are going to apply some min max it's basically a number based scenarios right so for that what we have to do we have to use the the number of fields so cost is basically a number of field because numerical values are getting displayed over here so if we are doing a sum if we are doing an average if we are doing a Min number Max number right so it makes a sense to implement the use case over here so what we are going to do in cm in cmdb underscore CI table we will find out the maximum number we will find on the minimum number in this 2784 okay and we will also sum all these uh numbers in a single go and we will also find out the average of all the records right that let's say if we have 2784 records then we total the cost and divide by that right but in Glad aggregation we don't need to implement formula by using the API methods we can directly achieve it okay so let's see how we can implement it so let's move to the code editor here background script so again let's I will write the code and we'll change something comment over here some Max okay and count we have already done right so these are the four aggregations which we are taking here in this scenario Okay so the form so the syntax is we can give any name for a particular object I am giving as count just for understanding new aggregate then I can say underscore CI because here we are talking about this table cmdb underscore CI right then what we have to do we have to root count dot add aggregate right the same first we have to give the aggregation that is sum and for what we have to do we have to give for the field say cost so field name is cost here you will also find that aggregate sum and field is string so if it is basically cost okay I am like aware of the field name you can go to the particular table and find out the field name back and field name right query then while what we have to do while count dot next and count Dot aggregated once we are using at aggregate then we have to use let aggregate too okay and the same thing which we are using over here same we have to use here also right here it is done now you are thinking it's very simple right writing a code same for all the aggregations right just info so we are done with the code let's execute run script oh it displays all the things not sum so what exactly it did it basically go to add aggregate sum and then filter all the uh cost with the same digits let's say go here if you go and you can Group by it see here Group by cost see so it is displaying me this 49.99 cost having 23 records right so in that way is basically displayed as the record but as per the use case we need the sum okay of all the costs so what for that what we have to do we have to write one more thing over here that is count Dot set group okay and we have to say false so if you want that we don't need to group by the things then we have to do this because it's basically figure out that the same data how many records it have okay by using this basically we are saying do not do that whatever things we have only add then run script so see here now we have a total sum of all the records of all the cost which is mentioned in 2700 84 records right in this way so in this way we can use some if you want to apply some filter to do a addition for a particular category or particular class only then we can do that also how we can do that let's say I want that for a Linux server I need only the cost sum so so much okay there is once uh I am writing a query so I am just copy query right here I can say add encoded query the same we use in live record right here we have to pass the displaying me the cost of Linux server only see here 47 4557.5 in that way so if I mark it as the calculations so in that way you can apply filter and you can perform the sum let's say if we have to do average the same thing the same code only we have to change the aggregation see 2092 point something something okay okay in the same way you can do min see very simple right so the Min value is zero let's see let's check go all this is descending order let's click over here now it's ascending order so it is taking the null as in zero okay so if I apply filter that do not consider uh this cost right filter out way I am saying cos is not equal to 0 then what is the minimum value that is 49.99 right copy query same in encoded query we can use it go here apply some filter next one see 41.99 is the minimum value available in all the records right same we can do for Max also so for Max what we have to do just comment it out change the aggregation only for thousand forty five thousand five I see here this is the max value right because we are sorting the particular list in descending order okay so in this way the use case which says that we have to see that how we can implement the aggregation that is some average Min and Max we have seen here right it's a very simple code and we know that if we are passing a particular field with a particular aggregation then it will Group by the things with the particular values first and then it will display the results right so accordingly we can use other filters other things as far as the requirement right now we have to implement the fourth use case that is distinct value okay this thing class value in single string comma separated I believe you are understanding that what exactly we want to display right I will give a brief of it so I am doing the same in cmdb underscore side tables of other what I am doing here is that I am not using this to submission instance we have all see here we have classes we have lanx Linux Linux line X line X right and we have lots of other classes so I want what all classes are used in this 2784 records only distinct classes means no duplicate classes needs to be displayed and it should be displayed me in a single line in a string format with comma separated let's say we have to display the number of classes uh record is consuming uh do I use in a particular message or we have to trigger in my notification where we have to uh tell them that okay these are the values which you are using in a single row right there we can use a lead aggregation okay let me tell you how we can do that so here I went to the editor now again there aggregate okay so the only game is of aggregation and the field okay so here the aggregation which we have to use that aggregation is group concat so here I am using group concat and group concat distinct Port but the requirement is of only group quantity distinct as per the use case but for displaying perspective I am using a group concat also okay so group concrete underscore conclude and for which field we have to use we have to use for class okay now we have now good concave again we have to open the queries must in that record even though in Glide aggregate because this only perform whatever syntax we have written above Dot next and I'm saying now dot get educated as I said the number of parameters which we have passed in had aggregate the same number of parameters we have to pass in get address again here and then script it is not displaying anything to us because we are not here oh it's taking time let's see what results okay displayed all the classes why because we have used group concat but if we want to remove the duplicates then we have to use distinct that is the main difference between group concat and group listing okay just to demonstrate actually I use book one cut as I said earlier what is displaying me separate us names Not Duplicate one see here all these classes basically used in this uh cmdb underscore CI table for 2700 from 84 records right yeah 2784 record so this number of classes has been used but as I have said earlier that we need in a single string and comma separated right but it is not displaying in the required format right for that what we have to do the same set group well that's good see here all the things comes into a single row it is basically not segregating the data now it is only displaying all of the classes in a distinct format right by using this in that way we can post in a single row all the data right so friends I think we have implemented all the aggregations right which is mentioned over there uh min max some average group quantity distinct group concat and we have also seen in the demonstration that how we can implement the same while writing the script in service now right I believe now you will be having a better idea of let Aggregate and you will be not going to forget it because it's very simple to use and very simple to implement right so friends I have also created one of the article where I have posted all the things along with code you can navigate to that article also uh where you will find all the information and the definitions and all things friends I have posted the above Link in the comment section too so you can like navigate to that link if you need more information so this is all for this video it was helpful then please like share and subscribe and please provide the suggestions feedback and if you have any questions in comment section below I will try to answer all your questions thank you friends have a nice day bye bye