SUM a subquery?

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, th开发者_如何学Goe 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

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.

Try this:

select b.id, b.prod_name, sum(bwe.quantity)*b.weight as reg_yr 
from bus_products b inner join 
bus_warehouse_entries bwe on bwe.org_product_code=b.prod_code 
where bwe.type=0 and bwe.request_date>='some date' 
group by b.id,b.prod_name, b.weight

UPDATE: How about this then?

SELECT
      bus_products.id
      , bus_products.prod_name
      , f.part_total  
   FROM
      bus_products bus_products 
      inner join (
         SELECT SUM(bus_warehouse_entries.quantity) * bus_products.weight as part_total
         ,bus_products.prod_code as p_code
         FROM bus_warehouse_entries bus_warehouse_entries join  bus_products 
         on bus_products.prod_code=bus_warehouse_entries.org_product_code
         WHERE bus_warehouse_entries.type = 0
         AND bus_warehouse_entries.request_date >= 'some_date'
         group by bus_products.prod_code,bus_products.weight
      )  f on f.p_code=bus_products.prod_code

Try wrapping the whole thing in another query.

SELECT id, prod_name, sum(reg_yr)
FROM (your query)
GROUP BY id, prod_name

To help support my answer, and your comments in the question, it sounds like you want the product NAME and not the ID... Ex: you could have 20 products that are all Televisions where the product name actually would sound like a category vs a specific product name. Each "product name" one would have more specific "what" about it. So, the ONLY reason you would need to join on the ID is to get the proper weight associated with warehouse inventory. That said, I would try this...

select 
      bp.prod_name,
      sum( wh.quantity * bp.weight ) as TotalWeight
   from
      bus_warehouse_entries wh
         join bus_products bp
            on wh.org_product_code = bp.prod_code
   where
          wh.type = 0
      and wh.request_date >= 'some_date'
      and (any other criteria)
   group by
      bp.prod_name
   order by
      bp.prod_name (or whatever else)