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

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

order by prod.sku DESC

CodePudding user response：

You can order by inside string_agg(sku,', ' order by sku) remove order by from your subquery

CodePudding user response：

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.

CodePudding user response：

I think I found the solution. I used my query as subquery:

SELECT * FROM 
(SELECT prod.asin, string_agg(p.sku,', ' ORDER BY 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(
10,11,12)) AS prod
LEFT JOIN
products AS p
ON prod.asin = p.asin
GROUP by prod.asin) AS res
ORDER BY skus