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
– GMB, Commented Sep 30, 2020 at 21:45
No, you cannot nest aggregate functions. You will have to use a subquery. — Laurenz Albe
– Laurenz Albe, Commented Oct 1, 2020 at 1:51
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: dbfiddle.uk/… — user330315
– user330315, Commented Oct 1, 2020 at 12:04

GMB · Accepted Answer · 2020-10-01 12:00:40Z

1

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}}

answered Oct 1, 2020 at 12:00

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Count values in array_agg

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related