Search code examples
postgresqlconcatenationaggregate

How to aggregate results based on all distinct combinations in a column?

I have a table where I try to aggreate results (Sum) based on all possible combinations of Product_Ids per Order (Order_Id). Anybody that can guide me here?

enter image description here

I'm a bit lost here, but I have tried to group the different combinations but don't manage to get the right results.

Solution

  • I think you would like to group the results by order_id:

    select array_agg(distinct product_id), sum(summ) total 
from stat 
group by order_id
order by total desc;

    The function array_agg(distinct product_id) helps to concatenate unique values of product_id grouping values by order_id.

    See the demo with all the details.