CODEWITHSUNDEEP
sqlpostgresql
John
John

Reputation: 353

SQL Group By subquery in postgresql

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

enter image description here

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

enter image description here

Any ideas?

Best regards

Upvotes: 0

Views: 329

Answers (2)

user330315
user330315

Reputation:

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;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269813

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(*);

Upvotes: 0

Related Questions