How can I do order by without using it in group by

February 5, 2022

I have a table called products and I would like to get all my products with same asin inside one row and concat their skus in one column I came up with something like this but I have problem with order by sku and other columns that are not inside group by. Is there any solution?

my sql:

SELECT prod.asin, string_agg(p.sku,', ') AS skus,
  SUM(p.amazon_inv_available) AS amazon_available_inv,
  SUM(p.amazon_inv_total) AS amazon_total_inv
FROM (
  SELECT id, asin, sku, amazon_inv_available, amazon_inv_total
  FROM products 
  WHERE store_id IN (12, 10, 11)
  ORDER BY sku
) AS prod
LEFT JOIN products AS p  ON prod.asin = p.asin
GROUP by prod.asin

dbfiddle

Desired result when order by prod.sku

order by prod.sku DESC

>Solution :

You can add ORDER BY to STRING_AGG() to specify the ordering of the concatenated values.

For example:

SELECT
  asin, 
  string_agg(sku,', ' order by id) AS skus,
  SUM(amazon_inv_available) AS amazon_available_inv,
  SUM(amazon_inv_total) AS amazon_total_inv
FROM products
WHERE store_id IN (12, 10, 11)
GROUP by asin
ORDER BY amazon_available_inv

Result:

 asin  skus        amazon_available_inv  amazon_total_inv 
 ----- ----------- --------------------- ---------------- 
 B     HH, QW      200                   400              
 A     AC, TT, DD  300                   600

See db<>fiddle.