Aggregate SubQuery

Kinetic ERP
1

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.

2

SUM (OpenQty) and Group by Order, and LineNum

3

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
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

4

Use a Single Subselect to get that value with the aggregate.

1 Like
5

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?

6

Add Order Num and Line as fields with group by selected, then one calculated field that is sum(order-shipped)

7

i dont know what that means but the at least my ignorance makes me smile

8

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?

9
order line release open qty total
1111 1 1 106 306
1111 1 2 200 306

You want something like this?

10

Put this in a calculated field. You don’t need to worry about group by.

sum(OrderRel.OurRelQty) over (partition by OrderRel.Ordernum, OrderRel.OrderLine)
1 Like
11

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.

12

i want order 1111, line 1, total 306.

13

i will give this a go. thank you

14

If you just want that, then remove the release from from your displayed columns, and you already have it.

1 Like
15

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.

16

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 :slight_smile:

1 Like
17

Nice :raised_hands: :raised_hands: