Thanks @dmitry.lychagin and @vsr1
Hi, @lyndondonz
Could you try the following query:
SELECT p.id, p.name,
(
SELECT l.entityId,
ARRAY_SUM(ARRAY_AGG(l.averageSales)) AS averageSales,
ARRAY_SUM(ARRAY_AGG(l.totalSales)) AS totalSales
FROM psh, psh.location AS l
GROUP BY l.entityId
) AS location,
(
SELECT t.entityId,
ARRAY_SUM(ARRAY_AGG(t.averageSales)) AS averageSales,
ARRAY_SUM(ARRAY_AGG(t.totalSales)) AS totalSales
FROM psh, psh.territories AS t
GROUP BY t.entityId
) AS territories
FROM company_analytics.products AS p
LET psh = (
SELECT psh.location, psh.territories
FROM company_analytics.`products-sales-histories` psh
WHERE META(p).id = psh.product
)
It uses LET clause to get sales history instead of LEFT OUTER JOIN / GROUP BY which makes it a bit easier to read and keeps variable ‘p’ in scope, so you could later add
ORDER BY p.`rank`.`level`, p.`rank`.`positionHi @dmitry.lychagin and @vsr1
Cool… it works thank you. 
it there an issue using SUM() ? though, ARRAY_SUM() is the same with SUM()?
Is it optimal to use LET than LEFT JOIN
Hi @lyndondonz,
SUM() is a supported aggregate function. I’m still investigating the exact cause of the above issue.
We discuss the difference between SUM() and ARRAY_SUM() in our documentation at
SQL-92 Aggregation Functions and Aggregation Functions.
When it comes to using LET vs LEFT OUTER JOIN, it all depends on the query. We rewrite LET into LEFT OUTER JOIN followed by GROUP BY if such transformation is possible (like in this particular case). If that rewriting happens then there’s no downside of using LET vs manually doing LEFT OUTER JOIN / GROUP BY.
Hi @dmitry.lychagin @vsr1,
Thank you so much for your patience and immediate reply ARRAY_SUM aggregate function really works for me
I have a slight problem this time aggregating multiple objects base on the GROUPING with did.
So on the query statement:
basically, psh.location and psh.territories are array type. so another field type is psh.sales which is object types.
Need some advise on how can I aggregate this type. SUM() doesn’t work at all 
LET psh = ( SELECT psh.location, psh.territories, psh.sales FROM company_analytics.`products-sales-histories` psh WHERE META(p).id = psh.product )
Here’s the object
"sales": {
"averageProductOrdered": 57.33,
"averageProductShipped": 56.25,
"averageSales": 45.72,
"totalProductOrdered": 116,
"totalProductShipped": 116,
"totalSales": 1472.23
}
Hi @lyndondonz,
Try adding the following subquery next to your location/territories subqueries:
( SELECT VALUE SUM(psh.sales.totalSales) FROM psh )[0] AS totalSales
The complete query would be:
SELECT p.id, p.name,
(
SELECT l.entityId,
ARRAY_SUM(ARRAY_AGG(l.averageSales)) AS averageSales,
ARRAY_SUM(ARRAY_AGG(l.totalSales)) AS totalSales
FROM psh, psh.location AS l
GROUP BY l.entityId
) AS location,
(
SELECT t.entityId,
ARRAY_SUM(ARRAY_AGG(t.averageSales)) AS averageSales,
ARRAY_SUM(ARRAY_AGG(t.totalSales)) AS totalSales
FROM psh, psh.territories AS t
GROUP BY t.entityId
) AS territories,
(
SELECT VALUE SUM(psh.sales.totalSales)
FROM psh
)[0] AS totalSales
FROM company_analytics.products AS p
LET psh = (
SELECT psh.location, psh.territories, psh.sales
FROM company_analytics.`products-sales-histories` psh
WHERE META(p).id = psh.product
)Hi @dmitry.lychagin and @vsr1,
Thank you again for the immediate response.
I have a question, I found it tricky. How can I filter base on the on the "aggregated fields " ? for example I will do:
WHERE location.totalSales = 315.50
I’ve tried it, its seems it doesn’t, is that possible to filter based the on the aggregated field ? Really need help
If you want to include or exclude some location from the returned location array then you could use HAVING clause in that subquery:
( SELECT l.entityId, averageSales, totalSales FROM psh, psh.location AS l GROUP BY l.entityId LET totalSales = ARRAY_SUM(ARRAY_AGG(l.totalSales)), averageSales = ARRAY_SUM(ARRAY_AGG(l.averageSales)) HAVING totalSales = 315.50 ) AS location,
Hi @dmitry.lychagin and @vsr1,
Thank you for your immediate response. Appreciated it .
What I mean on the filter is that:
Base on this query results:
SELECT p.id, p.name,
(
SELECT l.entityId,
ARRAY_SUM(ARRAY_AGG(l.averageSales)) AS averageSales,
ARRAY_SUM(ARRAY_AGG(l.totalSales)) AS totalSales
FROM psh, psh.location AS l
GROUP BY l.entityId
) AS location,
(
SELECT t.entityId,
ARRAY_SUM(ARRAY_AGG(t.averageSales)) AS averageSales,
ARRAY_SUM(ARRAY_AGG(t.totalSales)) AS totalSales
FROM psh, psh.territories AS t
GROUP BY t.entityId
) AS territories,
(
SELECT VALUE SUM(psh.sales.totalSales)
FROM psh
)[0] AS totalSales
FROM company_analytics.products AS p
LET psh = (
SELECT psh.location, psh.territories, psh.sales
FROM company_analytics.`products-sales-histories` psh
WHERE META(p).id = psh.product
)
I can filter the results by:
WHERE location.totalSales = 315.50
Hi @lyndondonz,
I’m a bit confused. location is an array in the results that contains several objects.
"location": [
{
"entityId": "company::location::0002",
"averageSales": 113.72,
"totalSales": 1300
},
{
"entityId": "company::location::0004",
"averageSales": 233.72,
"totalSales": 300
}
]
Therefore you cannot just use
WHERE location.totalSales = 315.50
Do you want to retain those products that have a sum of total sales over all locations equal to some number (315.50), or you’re attempting to transform the result to only retain those locations that have total sales matching your criteria?
Hi @dmitry.lychagin,
My bad, sorry to confused you.
Yes, I want to retain / filter those products that the equal to some number.
Heres my query:
SELECT
meta(`products`).id, `products`.name, `products`.rank,
(SELECT l.entityId,
ARRAY_SUM(ARRAY_AGG(l.`averageProductOrdered`)) AS averageProductOrdered,
ARRAY_SUM(ARRAY_AGG(l.`averageProductShipped`)) AS averageProductShipped,
ARRAY_SUM(ARRAY_AGG(l.`averageSales`)) AS averageSales,
ARRAY_SUM(ARRAY_AGG(l.`totalProductOrdered`)) AS totalProductOrdered,
ARRAY_SUM(ARRAY_AGG(l.`totalProductShipped`)) AS totalProductShipped,
ARRAY_SUM(ARRAY_AGG(l.`averageSales`)) AS totalSales
FROM psh, psh.location AS l
WHERE l.`entityId` = 'company::location::IEASTON'
GROUP BY l.`entityId`
)[0] AS location
FROM
company_analytics.`products` `products`
LET psh = (
SELECT psh.location, psh.territories, psh.sales
FROM company_analytics.`products-sales-histories` psh
WHERE META(products).id = psh.`product`
AND psh.`period` BETWEEN '2020-03-21T07:07:36.792Z' AND '2020-04-20T07:07:36.792Z'
)
WHERE products.`active` = true
LIMIT 1 OFFSET 0
I want to use and include the location.totalSales = 0 in the statement:
WHERE products.`active` = true AND location.totalSales = 315.50
but, it would gave me empty result. Can you use the LET variable in the WHERE clause ?
Hi @lyndondonz,
Try extracting location into a LET variable and then use that variable in your WHERE clause.
SELECT meta(`products`).id, `products`.name, `products`.rank, location
FROM company_analytics.`products` `products`
LET psh = (
SELECT psh.location, psh.territories, psh.sales
FROM company_analytics.`products-sales-histories` psh
WHERE META(products).id = psh.`product`
AND psh.`period` BETWEEN '2020-03-21T07:07:36.792Z' AND '2020-04-20T07:07:36.792Z'
),
location = (
SELECT l.entityId,
ARRAY_SUM(ARRAY_AGG(l.`averageProductOrdered`)) AS averageProductOrdered,
ARRAY_SUM(ARRAY_AGG(l.`averageProductShipped`)) AS averageProductShipped,
ARRAY_SUM(ARRAY_AGG(l.`averageSales`)) AS averageSales,
ARRAY_SUM(ARRAY_AGG(l.`totalProductOrdered`)) AS totalProductOrdered,
ARRAY_SUM(ARRAY_AGG(l.`totalProductShipped`)) AS totalProductShipped,
ARRAY_SUM(ARRAY_AGG(l.`averageSales`)) AS totalSales
FROM psh, psh.location AS l
WHERE l.`entityId` = 'company::location::IEASTON'
GROUP BY l.`entityId`
)[0]
WHERE products.`active` = true AND location.totalSales = 315.50
LIMIT 1 OFFSET 0