Pretty neat, one of those options I have seen, just not when I needed it 
Do you happen to know of any other ways to use aggregate functions outside of a subquery? For some reason I thought there was some other way, I just haven’t been able to find it.
What do you mean “outside of a subquery”? Do you mean without grouping?
You can use windowing functions to use aggregates without grouping. Here’s a video with a bunch of tips.
That is a great video. @Banderson really explains those things very thoroughly, even for an anti-coder like me.
Yes, I meant without having to create a subquery, in the top level. Thanks for the link, I’ll take a look!
You can group (that’s what is generally required to use aggregate functions) on the top level just fine. You don’t need to create a subquery to do that. I’m pretty sure there is some terminology being mixed up here.
Well you can but you’d need to group by every other field in the query no? I have tried grouping by say part number in order to create a calculated field using an aggregate function and seemed to always get errors.
I do know they have the advanced group by editor, but I always had trouble using that as well. I’ll watch your video and see what I might be missing.
That’s correct. If you are trying to add all of the stuff on 3 rows into a single value, where do you put that value? Traditional aggregates turn the 3 rows into 1 row, and you can’t do that without grouping everything.
Windowing functions will do the aggregate and put it in all 3 rows. The video will show you how to do that.
I’ve never dug into the advanced group by myself, so I’m not sure how that works. (I should probably do that one of these days)
Have a look at the attached to see if this version runs quicker - slightly different approach
EPA_PartOnHandReceived.baq (46.2 KB)
Sue! This seemed like a good start. I finished building the query out with what I needed and am getting a ton of duplicate rows. I tried to make the top level query distinct and am getting errors. Seems like it’s brought me to a similar place as my original query. Do you know how the CTE’s operate differently from Inner Subqueries in BAQs? I can’t really tell if it does anything different on the front end, seems like it may just change what’s going on on the backend. I really appreciate you spending time on it!