enter image description hereI want to capture only the 3 productscodes from string_agg since My purpose is to see the any three products which are purchased together.
SELECT DISTINCT "ORDERNUMBER", string_agg( "PRODUCTCODE",',') AS Pro_purchased_together
FROM "Sales_data"
WHERE "STATUS"::varchar= 'Shipped'
GROUP BY "ORDERNUMBER"
ORDER BY 2 DESC
Screenshots are typically not encouraged in SO. You should provide some mock-up data, the table create and insert statements, and then finally the desired outcome. All as formatted text.
I took some liberty in determining what your table may look like. Here is a possible solution.
The inner query will identify the orders which had at least 3 products. The outer query will then count the product combinations and order them in descending order. Limit to 1 if you want to see just one.
Schema (PostgreSQL v14)
select purchased_together, count(*)
from (
select ordernumber, string_agg(productcode, ',') as purchased_together
from sales_data
group by ordernumber
having count(*) >= 3
)z
group by purchased_together
order by 2 desc;
| purchased_together | count |
|---|---|
| a,b,c | 3 |
| a,b,c,d | 1 |
| b,c,d | 1 |
You need to better describe HOW to count products, such as order with products A, B, C and D. Should that be included with orders that have A, B and C? That's why mock-up data, detailed information and desired output is required.