SUM a subquery?

Question

Looked through questions with similar titles, but none quite covered my question. I have a query that looks something like this.

   SELECT
      bus_products.id
      , bus_products.prod_name
      , (
         SELECT SUM(bus_warehouse_entries.quantity) * bus_products.weight
         FROM bus_warehouse_entries
         WHERE bus_warehouse_entries.org_product_code = bus_products.prod_code
         AND bus_warehouse_entries.type = 0
         AND bus_warehouse_entries.request_date >= 'some_date_here'
      ) AS reg_yr
   FROM
      bus_products
   WHERE 1'some_search_params'
   GROUP BY bus_products.prod_name
   ORDER BY 'some_sort'

While I am grouping by product name, the subquery selects by matching product code. Multiple product codes may have the same product name. If there is multiple codes with the same name, the above query only grabs the quantity of the first code due to the grouping.

I would like to just add a SUM() around the subquery in order to get the total of all product codes with that particular product name, but that causes a syntax error at the beginning of the subqueries SELECT. any suggestions on how to accomplish this another way?

for simplifications sake, the tables look something like this

bus_products
id | prod_code | prod_name | weight

bus_warehouse_entries
org_product_code | quantity | type | request_date

Answer 1

SELECT x.prod_name,
    SUM(x.total)
FROM (
    SELECT bp.prod_name,
        (
            SELECT SUM(wh.quantity) * bp.weight
            FROM bus_warehouse_entries wh
            WHERE bp.prod_code = wh.org_product_code
        ) AS total
    FROM bus_products bp
) x
GROUP BY x.prod_name

You can add more subqueries in the select inside the from and sum them in the outside query.