Count values in array_agg

Question

Considering the following data:

|model| qty | color                              |
--------------------------------------------------
|  y  |  6  | {yellow,red,red}                   |
--------------------------------------------------
|  z  |  4  | {red,blue,yellow}                  |
--------------------------------------------------
|  x  |  9  | {yellow,yellow,yellow,blue,yellow} |
--------------------------------------------------

Is there a way of counting each "color" grouped in the array below, without using a subquery? (For "y" I would have "yellow:1", "red:2", for instance)

SELECT model, SUM(qty), ARRAY_AGG(color) FROM test GROUP BY model;

Something like:

SELECT model, SUM(qty), ARRAY_AGG(ARRAY[color, COUNT(*)::TEXT]) FROM test GROUP BY model;

Sample code:

CREATE TABLE test (model TEXT, qty DOUBLE PRECISION, color TEXT);

INSERT INTO test (model, qty, color) VALUES
('x', 1, 'yellow'),
('y', 1, 'yellow'),
('z', 2, 'red'),
('x', 3, 'yellow'),
('y', 1, 'red'),
('z', 1, 'blue'),
('x', 2, 'yellow'),
('x', 1, 'blue'),
('y', 4, 'red'),
('z', 1, 'yellow'),
('x', 2, 'yellow');

Please provide sample data and desired results. As for me, I don't understand the purpose of the pseudo code. — GMB, Sep 30 '20 at 21:45
No, you cannot nest aggregate functions. You will have to use a subquery. — Laurenz Albe, Oct 01 '20 at 01:51
@LaurenzAlbe I thought so. Just wondering if there's a workaround without the need for a subquery — douglas_forsell, Oct 01 '20 at 11:55
You can't do it with a single level of aggregation because you actually want to aggregate on two different levels: first on color level, then on model level. So I don't see any other way than to use a derived table. I would however aggregate this into a JSON object: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=8a27e79cce2246b981a6c6fb06ee071a — , Oct 01 '20 at 12:04

score 1 · Answer 1 · answered Oct 01 '20 at 12:00

You can do this with two levels of aggregation:

select model, sum(qty) qty, array_agg(array[color, qty::text]) colors
from (select model, color, sum(qty) qty from test group by model, color) t
group by model

I don't think that there is a simple way to do this without subqueries, and I don't see why you would want to avoid using a subquery in the first place.

Demo on DB Fiddle:

model | qty | colors                       
:---- | :-- | :----------------------------
z     | 4   | {{yellow,1},{blue,1},{red,2}}
y     | 6   | {{yellow,1},{red,5}}         
x     | 9   | {{yellow,8},{blue,1}}

Count values in array_agg

1 Answers1