Hi, I am trying to write a query to get the sum of requested quantities by date, location and product id but I just cant seem to make this work. I wrote the below query - It is not grouping all product ids together and summing up the req and fulfilled quantities. Ignore the where clause - i just gave it 3 orders to see how group by works.
SELECT DATE_FORMAT_STR(STR_TO_TZ(MILLIS_TO_UTC(o.received_date_time), 'US/Central'),'1111-11-11') as `Received Date`, o.order_location_id as `Location Id`, i.source_product_id as `Source Prod Id`, i.requested_material_name as `Material Name`, sum(i.requested_qty) as `Requested Qty`, sum(i.fulfillment.fulfilled_qty) as `Fulfilled Qty` from `order` o UNNEST item i where meta(o).id IN ["order1", "order2", "order3"] GROUP by o.received_date_time, o.order_location_id, i.source_product_id,i.requested_material_name ORDER by o.received_date_time, o.order_location_id,i.source_product_id;
|Fulfilled Qty||Location Id||Material Name||Received Date||Requested Qty||Source Prod Id|
I expect that the product sco with prod id 7866 should be combined into 2 rows and not 3. One row for location 12199 and one row for location 12205 with the qty summed up.
Any ideas on how to make this work?