mysql adding total price of certain products

Question

hi i'm having problems querying a products table at the moment:

i need to display the total cost of all HP and toshiba products..

this is what i have tried so far

SELECT * FROM products 
WHERE prod_id LIKE '__hp%'
AND SELECT SUM(price) AS total  FROM products;

any help would be appreciated

this is a pic of the products table -->>

Thank you;

Answer 1

You could help yourself a lot if it is clear that the 3rd and 4th characters of Prod_ID are a manufacturer code. HP and TA for Toshiba.

SELECT SUBSTRING(prod_id,3,2)
      ,SUM(price * on_hand)
 WHERE SUBSTRING(prod_id,2,1) IN ('TA','HP')
 GROUP BY SUBSTRING(prod_id,3,2)