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[0].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;
Results are:
Fulfilled Qty
Location Id
Material Name
Received Date
Requested Qty
Source Prod Id
1
12199
Rold
5/12/2020
1
7746
1
12205
Mun
5/12/2020
1
7747
1
12205
Sun
5/12/2020
1
7758
1
12205
Ruf
5/12/2020
1
7768
1
12199
Spi
5/12/2020
1
7797
1
12205
Fri
5/12/2020
1
7865
1
12205
Fri
5/12/2020
1
7865
1
12205
Sco
5/12/2020
1
7866
1
12199
Sco
5/12/2020
1
7866
2
12205
Sco
5/12/2020
2
7866
1
12205
Chi
5/12/2020
1
7867
1
12199
Sm
5/12/2020
1
7883
1
12205
Fun
5/12/2020
1
7884
1
12199
Dor
5/12/2020
1
7933
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.
It does. To verify project as o.received_date_time
OR
GROUP BY DATE_FORMAT_STR(STR_TO_TZ(MILLIS_TO_UTC(o.received_date_time), ‘US/Central’),‘1111-11-11’), o.order_location_id, i.source_product_id,i.requested_material_name
N1QL does not like DATE_FORMAT_STR(STR_TO_TZ(MILLIS_TO_UTC(o.received_date_time), ‘US/Central’),‘1111-11-11’) in Group By. It gives the below error:
“code”: 4210,
“msg”: “Expression must be a group key or aggregate: (o.received_date_time)”,
Putting o.received_date_time in the projection does not work either.
I am getting the exact same response as previous even after making the changes you suggested. If you think the query is correct - and I think it is - there seems to be something broken with N1QL. I guess I’ll open a support ticket.
SELECT dv 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[0].fulfilled_qty) as `Fulfilled Qty`
FROM `order` AS o USE KEYS ["order1", "order2", "order3"]
UNNEST item AS i
LET dv = DATE_FORMAT_STR(STR_TO_TZ(MILLIS_TO_UTC(o.received_date_time), 'US/Central'),'1111-11-11')
GROUP BY dv, o.order_location_id, i.source_product_id, i.requested_material_name
ORDER BY dv, o.order_location_id, i.source_product_id, i.requested_material_name;
If still have issue provide the 3 documents. You have support agreement please go ahead and open support ticket.
Thanks a lot for your help with this. The query you provided works as expected. I do feel that it would be desirable to give a function like DATE_FORMAT_STR in the Group by clause but the workaround you gave with the LET clause works. I realized my mistake in putting the received_date_time in the Group by clause. It is a unix epoch timestamp and it will be different for each order. In the data format function, I was extracting only the date. But since it did not work with Group by I plugged in received_date_time field in there forgetting that it is a timestamp not date . Wouldn’t have gone through all this trouble if the date function would have worked in the date clause in the first place! . Thanks again.
Not sure what version you have. You can also alias group column and see if that works.
SELECT dv 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[0].fulfilled_qty) as `Fulfilled Qty`
FROM `order` AS o USE KEYS ["order1", "order2", "order3"]
UNNEST item AS i
GROUP BY DATE_FORMAT_STR(STR_TO_TZ(MILLIS_TO_UTC(o.received_date_time), 'US/Central'),'1111-11-11') AS dv, o.order_location_id, i.source_product_id, i.requested_material_name
ORDER BY dv, o.order_location_id, i.source_product_id, i.requested_material_name;
We have 6.0.3. I’m getting an error when trying to alias group column.
“code”: 3000,
“msg”: “syntax error - at AS”,
I tested this against 6.5 and looks like 6.5 supports both Aliasing group columns as well as using functions in group by. I would not have run into this at all if we were on 6.5… time to upgrade!
6.0.3 supports expression in group by.
provide same expression in group by and projection. As you have order by (you can use projection alias or provide same expression in order by too)
. Wouldn’t have gone through all this trouble if the date function would have worked in the date clause in the first place! 
. Thanks again.