How can I SELECT other fields together with other Aggregate function in Analytics?

Analytics
,
1

Hi Guys,

In Analytics N1QL, how can I SELECT other fields together with Aggregate Function SUM().
I wan to SELECT other fields example ( META(p).id and p.name ) .
It seems that it produces an Error:

[
  {
    "code": 24041,
    "msg": "Cannot resolve alias reference for undefined identifier p (in line 3, at column 3)",
    "query_from_user": "SELECT\n  META(p).id,\n  p.name,\n  SUM(pa.sales) AS totalSales,\n  SUM(pa.qtyShipped) AS totalShipped\nFROM `company_analytics`.`products` AS p\nLEFT JOIN `company_analytics`.`product-aggregates` AS pa\n  ON META(p).id = pa.product\n  AND pa.periodAt BETWEEN '2020-01-01T06:00:00.000Z' AND '2020-08-28T23:25:53.178Z'\n  AND pa.entityId IN [ \n    \"territory::0000\",\n    \"territory::0001\",\n    \"territory::0002\",\n    \"territory::0003\",\n    \"territory::0004\",\n    \"territory::0005\",\n 
  }
]

Here is my query:

SELECT
  META(p).id,
  p.name,
  SUM(pa.sales) AS totalSales,
  SUM(pa.qtyShipped) AS totalShipped
FROM `company_analytics`.`products` AS p
LEFT JOIN `company_analytics`.`product-aggregates` AS pa
  ON META(p).id = pa.product
  AND pa.periodAt BETWEEN '2020-01-01T06:00:00.000Z' AND '2020-08-28T23:25:53.178Z'
  AND pa.entityId IN [ 
    "territory::0000",
    "territory::0001",
    "territory::0002",
    "territory::0003",
    "territory::0004",
    "territory::0005",
  ]
GROUP BY META(p).id

LIMIT 50
2

Hi @lyndondonz,

Try adding p.name to the GROUP BY list:
GROUP BY META(p).id, p.name

3

Hi @dmitry.lychagin,

Thanks Man! :slight_smile:
Cool it works, but what if I have another fields to add example( p.productCode, p,rank, p.unit, p.price )
would it be:
GROUP BY META(p).id, p.name, p.productCode, p,rank, p.unit, p.price ?
is there another simple way for this ? :slight_smile:

4

Hi @lyndondonz,

You could write the query without using GROUP BY:

SELECT
  META(p).id,
  p.name, p.productCode, p.rank, p.unit, p.price,
  totals.*
FROM `company_analytics`.`products` AS p
LET totals = (
  SELECT SUM(pa.sales) AS totalSales, SUM(pa.qtyShipped) AS totalShipped
  FROM `company_analytics`.`product-aggregates` AS pa
  WHERE META(p).id = pa.product
  AND pa.periodAt BETWEEN '2020-01-01T06:00:00.000Z' AND '2020-08-28T23:25:53.178Z'
  AND pa.entityId IN [ 
    "territory::0000",
    "territory::0001",
    "territory::0002",
    "territory::0003",
    "territory::0004",
    "territory::0005"
  ]  
)[0]
LIMIT 50
5

Hi @dmitry.lychagin,

Sorry for the late response, sorry I forgot to mention that I can ORDER BY totalSales and totalShipped and name
Is it possible to ORDER BY using the LET ?

6

Hi @lyndondonz,

Yes, you can use ‘totals’ variable in the ORDER BY. It’s value is an object
{ "totalSales": ..., "totalShipped": ... }
So you can use it as follows:
ORDER BY p.name, totals.totalSales, totals.totalShipped

Alternatively you can project these fields in the SELECT list and ORDER BY their SELECT aliases:

SELECT p.name AS name, totals.totalSales AS totalSales, totals.totalShipped AS totalShipped
...
ORDER BY name, totalSales, totalShipped
7

Hi @dmitry.lychagin,

Cool Thanks Man, It works :slight_smile: