Different Ways to Use Aggregate Functions


I have a query that was working just fine, we had a request to add some fields to the query and I now can’t get it to execute (locks up my machine). The original query already had a subquery where total quantity on hand was being calculated for each part, so just totaling all of the different onHandQtys in each bin for each part.

I now have added a new subquery to calculate the total received quantity of each part for that past year and this aggregate function has rendered the query inoperable. Are there any other tricks that anyone has figured out that might help a query to run more efficiently when it comes to aggregate functions? Or is there another way anyone knows of that I could total quantities for each part without it being a subquery? I feel like I’ve made much more complex queries with no issues, so not sure why this calculation bogs the system down.


Can you post screenshots of your subqueries?

Hi Alisa,

Using only RcvDtl here is the subquery that when added has caused the top level query to break.

Here’s the table criteria I have on RcvDtl.

The subquery is being joined to Part in the top level query.

Have you tried setting that subquery as a cte instead of inner to see if that helps at all? If you run the subquery as the top level does it come back or time out?

So far it’s looking like it’s running at about the same pace after changing the query to a CTE query. I did finally get my results, 800 rows took a little over 5 minutes to load. The subquery itself runs with 3,000 rows in a quarter of a second.

What are the joins between your top level and the subquery? You are joining on company right?

Yes, doesn’t seem to make a difference. We only have one company though so doesn’t really change anything.

Can you post a screenshot of the joins to the subquery?

Joining to rcvdtl from the subquery just by the part number.

Why are you joining to rcvdtl and then to your subquery? Get rid of that and join from part to your subquery.

I need another field from RcvDtl. I originally had the subquery joined to part and then RcvDtl joined to the subquery but switched those around to try and join on company. The subquery does not have Company in it.

If you need rcvdtl separately then directly join from part to the subquery. Chaining those together you are running the sum individually for each and every rcvdtl record that is returned. And always make sure you include company in the subquery and ALWAYS join on company. It doesn’t matter if you only have one company.

It doesn’t appear to have made it any faster. Thank you for taking the time to try and help me speed it up!

What does it look like now?

What version are you on @jott ? If you are on a new version, then you can try adding option(recompile) and see if that kicks it into gear.



1 Like

I’ll politely semi-disagree with that. Always try joining on company. However, sometimes (even usually) that also tanks query performance for inscrutable reasons. So also try joining without. Go with whichever is faster.

I think I’m still pretty much in the same place query wise.

@Banderson, I tried the recompile and after 5 or so minutes I’d get an error and 0 results. I removed it from the execution settings and am back to getting 800 results in 4 minutes. I’m on 2023.1.10 by the way.

What was the error?

It said something like Application Error: Application Server cannot be reached. Something to that effect. I ended up just splitting the query back up into my original query and made a second query containing the fields that were newly requested. Then made it so that the grid containing the new fields was “subscribed” to the original grid. It seems to work, still waiting for hear any feedback the users have.

That’s what “Specified table field value” is for. Just make sure the order of your tables puts the needed table first. Like this - this is a criteria on PartCost that is referencing Part:


So, I agree, but, if you skip the company-join, then add one of these to the table:

I’ve experienced this (a filter on Company) speeding things up for no good reason at all. That’s a @timshuwy trick, I’m pretty sure.