Query a column and a calculation of columns at the same time PostgreSQL

Question

Hi

I have two tables, Products and BundleProducts that have o2o relation with BaseProducts. A BundleProduct is a collection of Products using a m2m realtion to the Products table. Products has a price column and the price of a BundleProduct is calculated as the sum of the prices of its Products.

BaseProducts have columns like name and description so I can query it to get both Products and BundleProducts.

Is it possible to query and sort by price both for the price column of the Products and calculated price of the BundleProducts?

Thanks

Answer 1

Try something like this:

SELECT name, description, price
FROM (
    SELECT name, description, price FROM products
    UNION
    SELECT bundle_products.name, bundle_products.description, sum(products.price)
    FROM bundle_products
    JOIN products on (<your join condition)
    GROUP BY bundle_products.name, bundle_products.description
) AS combined
ORDER BY price