Select subquery with Aggregation

SQL++
,
1

Hi, I am working with a query hoping to get counts of an ARRAY_AGG function. I am looking for unique counts. So for this set of ARRAY_AGG data “ShipperAccountNumber”: [
“GI35I0”,
“GI35I0”,
“GI35I0”,
“GI35I0”,
“GI35I0”,
“GI35I0”,
“GI35I1”
],

I am looking to get GI35I0 : 6, and GI35I1 : 1, we are using this query currently to get a number of things included the array above

SELECT COUNT(Status) AS acct, Status, ARRAY_AGG(shipperAccountNumber) as C
FROM testCV
WHERE Date BETWEEN “2018-11-05” AND “2018-11-08” AND
sumber IN [‘GI35I0’, ‘GI35I1’]
GROUP BY Status;

We were able to get the data we wanted using a subquery

SELECT t1.Status,ARRAY_AGG(ARRAY_APPEND(t1.SNumber,t1.status)) AS INDVCOUNT,sum(t1.status) AS status FROM
(
SELECT Status,count(Status) AS status,ARRAY_AGG(distinct sNumber) as SNumber
FROM testCV
WHERE Date BETWEEN “2018-11-05” AND “2018-11-08” AND
sNumber IN [‘GI35I0’, ‘GI35I1’]
GROUP BY Status,sNumber
) AS t1 group by t1.Status

But we are looking to get the query in another way maybe using a subquery in the original queries select, using an alias, etc.

2 
SELECT d.cnt, d.Status, (OBJECT v.sac:v.cnt FOR v IN sv END).*
FROM (SELECT COUNT(t.Status) AS cnt, t.Status, ARRAY_AGG(t.shipperAccountNumber) AS av
      FROM testCV AS t
      WHERE t.Date BETWEEN "2018-11-05" AND "2018-11-08" AND t. ShipperAccountNumber IN ["GI35I0", "GI35I1"]
      GROUP BY t.Status) AS d
LET sv = (SELECT a AS sac, COUNT(1) AS cnt
          FROM d.av AS a
          GROUP BY a);

You can use one of this based on your representation
If you need as Array replace (OBJECT v.sac:v.cnt FOR v IN sv END).* with sv OR
(OBJECT v.sac:v.cnt FOR v IN sv END).* with ARRAY { v.sac:v.cnt} FOR v IN sv END

3

Sorry for being a bit unclear, but I am looking for the response to still partially be an array and not necessarily the number be the identifier, was just looking for the count, for example. The partial output of the modified query is
[
{
“GI35I0”: 6,
“GI35I1”: 1,
“cnt”: 7,
“packageStatus”: “In-Transit”
},
]

We are looking for the below,

[
{
“innerCounts” : [
{
“acct” : “GI35I0”,
“cnts” : 6
},
{
“acct” : “GI35I1”
“cnts” : 1,
}
]
“cnt”: 7,
“packageStatus”: “In-Transit”
}

]

4 
SELECT d.cnt, d.Status, (SELECT a AS acct, COUNT(1) AS cnts
                         FROM d.av AS a
                         GROUP BY a) AS innerCounts
FROM (SELECT COUNT(t.Status) AS cnt, t.Status, ARRAY_AGG(t.shipperAccountNumber) AS av
      FROM testCV AS t
      WHERE t.Date BETWEEN "2018-11-05" AND "2018-11-08" AND t.shipperAccountNumber IN ["GI35I0", "GI35I1"]
      GROUP BY t.Status) AS d;

OR

SELECT d.Status, SUM(d.cnts) AS cnt, ARRAY_AGG({d.acct, d.cnts}) AS innerCounts
FROM (SELECT t.Status, t.shipperAccountNumber AS acct, COUNT(t.Status) AS cnts
      FROM testCV AS t
      WHERE t.Date BETWEEN "2018-11-05" AND "2018-11-08" AND t.shipperAccountNumber IN ["GI35I0", "GI35I1"]
      GROUP BY t.Status, t.shipperAccountNumber) AS d
GROUP BY d.Status;
1 Like
5

This is what we are looking for! Thanks!

6

vsr1, we are working on the query I uploaded on the forum here, and want to essentially see if it can be combined with another 2 queries.

SO the query below is the one we optimized to give us a result set in the forum above.

SELECT d.pkgLatestStatus, SUM(d.cnts) AS totalAcyCount, ARRAY_AGG({d.acct, d.cnts}) AS innerCounts
FROM (SELECT t.pkgLatestStatus, t.shipperAccountNumber AS acct, COUNT(t.pkgLatestStatus) AS cnts
FROM cv AS t
WHERE t.shipperAccountNumber IN [“00030GH123”,“00026GH123”]
AND t.manifestDate BETWEEN “2018-09-13” AND “2019-01-10”
GROUP BY t.pkgLatestStatus, t.shipperAccountNumber) AS d
GROUP BY d.pkgLatestStatus;

We would like to see if we can add the two queries below to this and have one large query nested with subqueries. Is this doable?

SELECT d.healthStatus, SUM(d.cnts) AS totalAcyCount, ARRAY_AGG({d.acct, d.cnts}) AS innerCounts
FROM (SELECT t.healthStatus, t.subStatus, t.shipperAccountNumber AS acct, COUNT(t.healthStatus) AS cnts
FROM cv AS t
AND t.shipperAccountNumber IN [“00030GH123”,“00026GH123”]
AND t.manifestDate BETWEEN “2018-12-26” AND “2019-01-10”
GROUP BY t.healthStatus ) AS d
GROUP BY d.subStatus;

SELECT d.pdscCatCode, SUM(d.cnts) AS totalAcyCount, ARRAY_AGG({d.acct, d.cnts}) AS innerCounts
FROM (SELECT t.pdscCatCode, t.shipperAccountNumber AS acct, COUNT(t.pdscCatCode) AS cnts
FROM cv AS t
WHERE t.pkgLatestStatus = “03”
AND t.shipperAccountNumber IN [“00030GH123”,“00026GH123”]
AND t.manifestDate BETWEEN “2018-12-26” AND “2019-01-10”
GROUP BY t.pdscCatCode, t.shipperAccountNumber) AS d
GROUP BY d.pdscCatCode;

Thanks in advance

7

The second query is not right can u check. Because inner select projection you have t.subStatus, t.shipperAccountNumber AS acct. Without those in group by you can’t project.

If we combine into single query is enough? NOTE: each query block execute independently. i.e. each can use different index snapshot. Are u looking all queries use exact same snapshot