SQL query with array of counts?

Question

I have a table which looks like this:

record no   firstType  secondtype   win?
1               X          A         1
2               X          A         0
3               X          B         1
4               Y          B         0
5               Y          B         1
6               X          B         1
7               X          B         1

and what I need output is this.

firstType   secondType   winCounts
   X           [A,B]     [A:1,B:3]
   Y            [B]      [B:1]

So notice how the arrays under secondType tell where they OCCURED with firstType, while the arrays under winCounts tell how many wins of each secondType came with each firstType.

I can make the arrays using ARRAY_AGG but I'm lost for any possible way to make the winCounts column.

Tag your question with the database you are using. – Gordon Linoff Mar 17 '21 at 01:08 — Gordon Linoff, Mar 17 '21 at 01:08

score 0 · Answer 1 · answered Mar 17 '21 at 01:10

Use two levels of aggregation:

select firsttype, array_agg(secondtype order by secondtype),
       array_agg(secondtype || ':' || wins order by secondtype)
from (select firsttype, secondtype, sum(win) as wins
      from t
      group by firsttype, secondtype
     ) t
group by firsttype;

score 0 · Answer 2 · answered Mar 17 '21 at 02:20

Here's a more-complicated solution with a lambda method, because why not:

SELECT 
    PP.firstType AS "firstType"
,    ARRAY_DISTINCT(
         ARRAY_AGG(PP.secondType)
     ) AS "secondType"

,    ZIP_WITH(
         ARRAY_DISTINCT(
             ARRAY_AGG(PP.secondType)
         ),
         ARRAY_AGG(PP.count_str),
         (x, y) -> x || ':' || y
    ) AS "winCount"
FROM (
  SELECT 
     firstType
  ,  secondType
  ,  CAST(SUM("win?") AS VARCHAR(5))
  FROM dataTable
  WHERE "win?" > 0
  GROUP BY 
     firstType
  ,  secondType
) AS PP (firstType, secondType, count_str)
GROUP BY PP.firstType;

SQL query with array of counts?

2 Answers2