CODEWITHSUNDEEP
sqlselectaggregateamazon-athena
alvas
alvas

Reputation: 122228

How to aggregate all columns into array in SQL when using group-by?

Given a table like:

item vietnamese cost unique_id
fruits trai cay 10 abc123
fruits trai cay 8 foo99
fruits trai cay 9 foo99
fruits trai cay 12 abc123
fruits trai cay 14 abc123
vege rau 3 rr1239
vege rau 3 rr1239

Usually when it's not group by I can do:

SELECT * from mytable

But if I had use group by to group the rows with the same item name, I can't directly do:

SELECT 
item, 
array_agg(vietnamese) as vietnamese
array_agg(cost) as cost
array_agg(unique_id) as unique_id
from mytable
GROUP BY item

Is there an option to automatically all columns in SQL array_agg(*)?

Upvotes: 0

Views: 1307

Answers (1)

Theo
Theo

Reputation: 132922

No there is no syntax to do what you describe. E.g. COUNT(*) does not mean COUNT(a), COUNT(b), COUNT(c), …, the star in that context does not refer to an expansion of columns.

In general, SQL is pretty bad at meta-programming, i.e. using runtime information to modify queries. You can't select columns that all start with "a", or tables that match a pattern. SQL is very static, with a few exceptions, like SELECT *.

Upvotes: 2

Related Questions