Postgres, order by SUM of column in child table-CodePudding

I have the following (simplified) tables

products - id name

order_products - quantity, product_id

I would like to order the rows from products table based on the sum of quantity column in order_products for every product

If order_products has these rows

quantity - product_id
2        - 555
2        - 555
6        - 666
3        - 777

I want the results to be ordered like that: 666, 555, 777

666 has 6 quantity, 555 has 4 and 777 has 3

I ended up using slightly modified version of the answer, as my requirement was to include products even if they don't have any relations in order_products table.

select p.*, coalesce(sum(op.quantity), 0) quantity_sold
from products p
left outer join order_products as op on op.product_id = p.id
group by p.id
order by quantity_sold desc

CodePudding user response：

Just use ORDER BY and aggregate function

select p.product_id, p.name, sum(quantity) quantity_sold
from order_products op
join products p on op.product_id = p.product_id
group by p.product_id, p.name
order by sum(quantity) desc