CODEWITHSUNDEEP
sqliteconcatenationaggregate
Finn Andersen
Finn Andersen

Reputation: 369

aggregating and concatenating by 2 identifiers

There's a particular SQLIte command I want to run to perform a aggregate and concatenate operation.

I need to aggregate by "ID" column, then for each ID, concatenate unique 'Attribute' and also concatenate average of 'Value' for each unique corresponding 'Attribute':

Example Table

I can do concatenate unqiue Attribute and aggregate by ID, but haven't got average of Value working.

Upvotes: 0

Views: 43

Answers (1)

alexander.polomodov
alexander.polomodov

Reputation: 5534

Try to use subquery for getting AVG for combination of id+attribute and then use group_concat:

select t.id, Group_Concat(t.attribute) as concat_att, Group_Concat(t.avg) as concat_avg from 
(
    select test.id, test.attribute, AVG(test.value) as avg from test
    group by test.id, test.attribute
) as t group by t.id;

See this example here: http://sqlfiddle.com/#!7/03fe4b/17

Upvotes: 1

Related Questions