# Group By and Aggregate functions? **URL:** \ **Category:** Kinetic ERP\ **Tags:** baq, kinetic\ **Created:** [October 13, 2025, 3:51pm UTC](https://www.epiusers.help/t/group-by-and-aggregate-functions/129950 "2025-10-13T15:51:59Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![Csciarrino](https://www.epiusers.help/letter_avatar_proxy/v4/letter/c/edb3f5/32.png) [@Csciarrino](https://www.epiusers.help/u/Csciarrino)\ **Post date:** [October 13, 2025, 3:51pm UTC](https://www.epiusers.help/t/group-by-and-aggregate-functions/129950/1 "2025-10-13T15:51:59Z")
So I’m not well versed in query language, so forgive my ignorance but I’m so lost with the whole Group By and Aggregate function thing. I’m just trying to make a BAQ that gives me a list of all my parts and part rev data but only for the latest rev. So I assumed that I can create a calculated function that gets the MAX part rev and group by the part rev. I did that in a subquery because there is a bunch of other stuff I need from other tables. and just keep getting that PartRev.PartNum isn’t in an aggregate function or group by, but if I include it in the group by I get multiple entries in my subquery and not the latest rev only. ![image](https://www.epiusers.help/uploads/default/original/3X/9/8/98a51942af7ed0a68f527da93bee33b7b82966ec.png) Does anyone have any resources to help get a better grasp of what the aggregate stuff is and what it means as well as help to get a deeper understanding of what the group by clause does? I feel like I’m not understanding the core functionality/concepts of those which is throwing me. ---
**Author:** ![TDray](https://www.epiusers.help/user_avatar/www.epiusers.help/tdray/32/42812_2.png) [@TDray](https://www.epiusers.help/u/TDray)\ **Post date:** [October 13, 2025, 4:07pm UTC](https://www.epiusers.help/t/group-by-and-aggregate-functions/129950/2 "2025-10-13T16:07:37Z")
There are some potential issues in that PartRev.RevisionNum is a string instead of a number, but for the most part it shoulld work. If I’m reading correctly, this is what you are facing: ![image](https://www.epiusers.help/uploads/default/original/3X/7/5/7566d1b9e315490f0d839593f45e9f7e4c993f45.png) This is actually an easy fix, but it’s confusing to read. The key is to just hit ok and then click the group by box here: ![image](https://www.epiusers.help/uploads/default/original/3X/f/9/f94755f005f5afcd01d861088d14ea1effdb063d.png) This should give you what you are looking for on this query. Trying to see if I can dig up something that explains things a little better. ---
**Author:** ![Csciarrino](https://www.epiusers.help/letter_avatar_proxy/v4/letter/c/edb3f5/32.png) [@Csciarrino](https://www.epiusers.help/u/Csciarrino)\ **Post date:** [October 13, 2025, 4:14pm UTC](https://www.epiusers.help/t/group-by-and-aggregate-functions/129950/3 "2025-10-13T16:14:38Z")
Yeah, that was my thought! But the issue is that once I add PartNum to the Group By, I end up with multiple entries (2 entries) for the same part instead of it just showing a singular entry for the latest rev. ☹ ---
**Author:** ![TDray](https://www.epiusers.help/user_avatar/www.epiusers.help/tdray/32/42812_2.png) [@TDray](https://www.epiusers.help/u/TDray)\ **Post date:** [October 13, 2025, 4:16pm UTC](https://www.epiusers.help/t/group-by-and-aggregate-functions/129950/4 "2025-10-13T16:16:48Z")
What all fields are you displaying, just the partnum and calculated max rev and what tables are you including? You should only need the partrev table itself to get this. If you are trying to pull more information, you need to put this into a subquery to feed into the top as once you add in other fields, you will get more rows per part. ---
**Author:** ![Banderson](https://www.epiusers.help/user_avatar/www.epiusers.help/banderson/32/50875_2.png) [@Banderson](https://www.epiusers.help/u/Banderson)\ **Post date:** [October 13, 2025, 4:20pm UTC](https://www.epiusers.help/t/group-by-and-aggregate-functions/129950/5 "2025-10-13T16:20:20Z")
You’ve got quite a bit of learning to do! What you want to do is definitely doable, but a bit hard to explain. I do have this recording that explains a bunch of different techniques that might be a good place for you to get to a better place where at least you can understand enough to be able to ask the right questions. > [@BAQ Calculated fields insights presentation](https://www.epiusers.help/t/baq-calculated-fields-insights-presentation/103274): > > Hey Everyone, This is a recording done after the fact of my insights presentation due to the technical difficulties I had during the presentation. Now I was able to record all of the live things that I wanted to show. I will apologize for my fat head in the way for some of it, but I thought it would be better if you could see me talking instead of just a disembodied voice. And I didn’t feel like taking the time to figure out how to use active presenter well enough to do some editing, so you ge… ---
**Author:** ![TDray](https://www.epiusers.help/user_avatar/www.epiusers.help/tdray/32/42812_2.png) [@TDray](https://www.epiusers.help/u/TDray)\ **Post date:** [October 13, 2025, 4:21pm UTC](https://www.epiusers.help/t/group-by-and-aggregate-functions/129950/6 "2025-10-13T16:21:59Z")
This is what I was looking for but couldn’t find. Great resource. I learned a bunch from it. ---
**Author:** ![Banderson](https://www.epiusers.help/user_avatar/www.epiusers.help/banderson/32/50875_2.png) [@Banderson](https://www.epiusers.help/u/Banderson)\ **Post date:** [October 13, 2025, 4:22pm UTC](https://www.epiusers.help/t/group-by-and-aggregate-functions/129950/7 "2025-10-13T16:22:56Z")
I have a hell of a time finding it every time I want to reference it too, and I posted the darn thing! ---
**Author:** ![Csciarrino](https://www.epiusers.help/letter_avatar_proxy/v4/letter/c/edb3f5/32.png) [@Csciarrino](https://www.epiusers.help/u/Csciarrino)\ **Post date:** [October 14, 2025, 3:17pm UTC](https://www.epiusers.help/t/group-by-and-aggregate-functions/129950/8 "2025-10-14T15:17:10Z")
Unfortunately there are a TON of fields I’m bringing in from multiple tables…such as the following (and more): - Non-stock - VMI - Part Class - Lot Size - Backflush - Rev - Track by Rev - IUM - PUM - SUM - Phantom BOM - Purchase to max - Track Lots - Costing Method - Track Serial # - Run Out - Inactive - Primary Warehouse - Primary Bin - etc… Essentially I need stuff from part, partplant, and partrev(so far) and need the data to be consolidated by the latest rev only, The other struggle is that we have 2 different sites and I’m getting the sense that folks want to not show both as well. ---
**Author:** ![Csciarrino](https://www.epiusers.help/letter_avatar_proxy/v4/letter/c/edb3f5/32.png) [@Csciarrino](https://www.epiusers.help/u/Csciarrino)\ **Post date:** [October 14, 2025, 3:17pm UTC](https://www.epiusers.help/t/group-by-and-aggregate-functions/129950/9 "2025-10-14T15:17:46Z")
Thank you! Ill def take a look at this. Appreciate the resource! ---
**Author:** ![Banderson](https://www.epiusers.help/user_avatar/www.epiusers.help/banderson/32/50875_2.png) [@Banderson](https://www.epiusers.help/u/Banderson)\ **Post date:** [October 14, 2025, 3:58pm UTC](https://www.epiusers.help/t/group-by-and-aggregate-functions/129950/10 "2025-10-14T15:58:27Z")
You have a couple of ways to do that. One is the windowing functions filtering technique that I show in the video. The other is to make a subquery with only your selection fields (Company, part number, max(rev) etc.) that ONLY has what you need to ID the proper record. Then you bring that into your top level and re-join it to your table so that selection sub filters the info table so you can get the other extraneous data that you need without the pesky problems with the extra fields making group by not work because of non-matching data I would probably recommend the latter, as it’s a bit simpler to create, and probably more efficient.