SQL Group By subquery in postgresql

Question

I want to group by the foolowing table but I also need to group the column ID as depitected on the image.

SELECT SUM(ml),sku,name FROM consumos 
GROUP BY sku,name
ORDER BY name

Any ideas?

Best regards

Please do not post code as images. See here for more details why: meta.stackoverflow.com/questions/285551 — user330315
– user330315, Commented Nov 14, 2020 at 13:08

user330315 · Accepted Answer · 2020-11-14 14:27:24Z

1

Looks like you want a JSON array. This can be done using jsonb_agg():

select name, sku, ml, jsonb_agg(id)
from the_table
group by name, sku, ml;

answered Nov 14, 2020 at 14:27

user330315

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

Comments

Gordon Linoff · Accepted Answer · 2020-11-14 14:26:39Z

0

In standard SQL, this would look like:

select name, sku, ml,
       '[' || listagg(id, ',') within group (order by id) || ']' as ids
from t
group by name, sku, ml
order by name, count(*);

However, not all databases support that standard operator || for string concatenation. And not all databases call their string aggregation operator listagg(). So you might need to tweak the query for your database.

EDIT:

In Postgres, this would be:

select name, sku, ml,
       '[' || string_agg(id, ',' order by id)  || ']' as ids
from t
group by name, sku, ml
order by name, count(*);

edited Nov 14, 2020 at 14:26

answered Nov 14, 2020 at 13:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

John Over a year ago

Hi, i am using postgresql. Thanks

Collectives™ on Stack Overflow

SQL Group By subquery in postgresql

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related