# Aggregate SubQuery **URL:** \ **Category:** Kinetic ERP\ **Tags:** baq\ **Created:** [April 25, 2022, 7:15pm UTC](https://www.epiusers.help/t/aggregate-subquery/89812 "2022-04-25T19:15:25Z")\ **Posts on this page:** 17\ **Page:** 1
**Author:** ![lyz.j](https://www.epiusers.help/user_avatar/www.epiusers.help/lyz.j/32/44214_2.png) [@lyz.j](https://www.epiusers.help/u/lyz.j)\ **Post date:** [April 25, 2022, 7:15pm UTC](https://www.epiusers.help/t/aggregate-subquery/89812/1 "2022-04-25T19:15:26Z")
Day of brain death, i swear. testing a very small query to get my logic straight before rolling it into a larger BAQ. One table OrderDtl - just displays OrderNum, LineNum, PartNum. i am trying to make a subquery with the OrderRel table. i have an order that has two open releases. one release is a partial ship and the other is open order 11111, line 1, release 1, openqty is 106 (ourreqqty-ourstockshipqty) order 11111, line 1. release 2, open qty is 200. i want to pass the value of 306 to the main query and i keep getting both release lines showing up in the main query. i have tried creating a calc field to get the 106 and 200 and then another calc field to sum the those numbers. i have also tried just a sum of the (ourreqqty-ourstockshipqty). i have joined the subquery (that has just OrderRel) to the OrderDtl main query by order & line. with inner, and outer joins. makes no diff. i have tried various group by’s with no luck. i am sure this is so stupidly simple, and am embarrassed to post this, but i am i am in short-circuit mode. ---
**Author:** ![josecgomez](https://www.epiusers.help/user_avatar/www.epiusers.help/josecgomez/32/55731_2.png) [@josecgomez](https://www.epiusers.help/u/josecgomez)\ **Post date:** [April 25, 2022, 7:18pm UTC](https://www.epiusers.help/t/aggregate-subquery/89812/2 "2022-04-25T19:18:56Z")
SUM (OpenQty) and Group by Order, and LineNum ---
**Author:** ![lyz.j](https://www.epiusers.help/user_avatar/www.epiusers.help/lyz.j/32/44214_2.png) [@lyz.j](https://www.epiusers.help/u/lyz.j)\ **Post date:** [April 25, 2022, 7:36pm UTC](https://www.epiusers.help/t/aggregate-subquery/89812/3 "2022-04-25T19:36:20Z")
You are so kind to post back so quickly. the first is the subquery that will throw errors if i don’t group by everything but calculated fields. ![image](https://www.epiusers.help/uploads/default/original/3X/f/a/fa7230db0e4868be5d2971007a4207ee80e23850.png) the main query throws error unless i add part number and calc field from subquery to the group by field, and then i am back to two lines for a reult ![image](https://www.epiusers.help/uploads/default/original/3X/9/e/9eb66d680ce1565094c9daaf9f726bc1e3beeaab.png) ---
**Author:** ![josecgomez](https://www.epiusers.help/user_avatar/www.epiusers.help/josecgomez/32/55731_2.png) [@josecgomez](https://www.epiusers.help/u/josecgomez)\ **Post date:** [April 25, 2022, 7:39pm UTC](https://www.epiusers.help/t/aggregate-subquery/89812/4 "2022-04-25T19:39:06Z")
Use a Single Subselect to get that value with the aggregate. ---
**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:** [April 25, 2022, 7:41pm UTC](https://www.epiusers.help/t/aggregate-subquery/89812/5 "2022-04-25T19:41:26Z")
Group by will group by everything that’s displayed, so you can’t group by order and line, then also display the release. How would you imagine that would be displayed in the grid? ---
**Author:** ![livingstonmarc](https://www.epiusers.help/letter_avatar_proxy/v4/letter/l/7feea3/32.png) [@livingstonmarc](https://www.epiusers.help/u/livingstonmarc)\ **Post date:** [April 25, 2022, 7:41pm UTC](https://www.epiusers.help/t/aggregate-subquery/89812/6 "2022-04-25T19:41:39Z")
Add Order Num and Line as fields with group by selected, then one calculated field that is sum(order-shipped) ---
**Author:** ![lyz.j](https://www.epiusers.help/user_avatar/www.epiusers.help/lyz.j/32/44214_2.png) [@lyz.j](https://www.epiusers.help/u/lyz.j)\ **Post date:** [April 25, 2022, 7:43pm UTC](https://www.epiusers.help/t/aggregate-subquery/89812/7 "2022-04-25T19:43:52Z")
i dont know what that means but the at least my ignorance makes me smile ---
**Author:** ![josecgomez](https://www.epiusers.help/user_avatar/www.epiusers.help/josecgomez/32/55731_2.png) [@josecgomez](https://www.epiusers.help/u/josecgomez)\ **Post date:** [April 25, 2022, 7:46pm UTC](https://www.epiusers.help/t/aggregate-subquery/89812/8 "2022-04-25T19:46:38Z")
Let’s back up, you are trying to show the SUM of 2 releases in your query (I think) but the query you have written is “By Release” meaning 1 Row per Release. That is not really possible unless you do something like a single subselect but then you’ll end up with 2 rows one for each release each of which says 302 Can you explain a little more what you are trying to accomplish? ---
**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:** [April 25, 2022, 7:53pm UTC](https://www.epiusers.help/t/aggregate-subquery/89812/9 "2022-04-25T19:53:03Z")
| order | line | release | open qty | total | | --- | --- | --- | --- | --- | | 1111 | 1 | 1 | 106 | 306 | | 1111 | 1 | 2 | 200 | 306 | You want something like this? ---
**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:** [April 25, 2022, 7:54pm UTC](https://www.epiusers.help/t/aggregate-subquery/89812/10 "2022-04-25T19:54:29Z")
Put this in a calculated field. You don’t need to worry about group by. ```auto sum(OrderRel.OurRelQty) over (partition by OrderRel.Ordernum, OrderRel.OrderLine) ``` ---
**Author:** ![lyz.j](https://www.epiusers.help/user_avatar/www.epiusers.help/lyz.j/32/44214_2.png) [@lyz.j](https://www.epiusers.help/u/lyz.j)\ **Post date:** [April 25, 2022, 7:54pm UTC](https://www.epiusers.help/t/aggregate-subquery/89812/11 "2022-04-25T19:54:39Z")
I am attempting to get a list of order number, line number, part number, (from main query of OrderDtl) main query and total quantity outstanding from OrderRel subquery. i had thought the best way to do this was have a subquery of the OrderRel table that calculated what was open on each release line, sum that, and just pass the single number to the main query that has other fields. ---
**Author:** ![lyz.j](https://www.epiusers.help/user_avatar/www.epiusers.help/lyz.j/32/44214_2.png) [@lyz.j](https://www.epiusers.help/u/lyz.j)\ **Post date:** [April 25, 2022, 7:55pm UTC](https://www.epiusers.help/t/aggregate-subquery/89812/12 "2022-04-25T19:55:34Z")
i want order 1111, line 1, total 306. ---
**Author:** ![lyz.j](https://www.epiusers.help/user_avatar/www.epiusers.help/lyz.j/32/44214_2.png) [@lyz.j](https://www.epiusers.help/u/lyz.j)\ **Post date:** [April 25, 2022, 7:55pm UTC](https://www.epiusers.help/t/aggregate-subquery/89812/13 "2022-04-25T19:55:49Z")
i will give this a go. thank you ---
**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:** [April 25, 2022, 7:56pm UTC](https://www.epiusers.help/t/aggregate-subquery/89812/14 "2022-04-25T19:56:33Z")
> [@lyz.j](#): > > i want order 1111, line 1, total 306… If you just want that, then remove the release from from your displayed columns, and you already have it. ---
**Author:** ![lyz.j](https://www.epiusers.help/user_avatar/www.epiusers.help/lyz.j/32/44214_2.png) [@lyz.j](https://www.epiusers.help/u/lyz.j)\ **Post date:** [April 25, 2022, 8:20pm UTC](https://www.epiusers.help/t/aggregate-subquery/89812/15 "2022-04-25T20:20:17Z")
thank you all for your input. this is just a fail from the start.my final output should be order#, Line#, part#, total open. i don’t get why i cannot make this work and i will try again tomorrow. ---
**Author:** ![lyz.j](https://www.epiusers.help/user_avatar/www.epiusers.help/lyz.j/32/44214_2.png) [@lyz.j](https://www.epiusers.help/u/lyz.j)\ **Post date:** [April 26, 2022, 12:35pm UTC](https://www.epiusers.help/t/aggregate-subquery/89812/16 "2022-04-26T12:35:25Z")
i was making this so over-complicated. a good night’s rest and the subQ is just what you suggested… i then take that total and do the math in an upper level query and get the reult i am expecting. now i will roll it into the main BAQ and see what happens 🙂 ---
**Author:** ![utaylor](https://www.epiusers.help/user_avatar/www.epiusers.help/utaylor/32/64664_2.png) [@utaylor](https://www.epiusers.help/u/utaylor)\ **Post date:** [April 26, 2022, 5:22pm UTC](https://www.epiusers.help/t/aggregate-subquery/89812/17 "2022-04-26T17:22:48Z")
Nice 🙌 🙌