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 -->>

products table

Thank you;

Mureink answer must be enough, but if in any momento you have a producto named "HP5 Epson bla bla" it will fail. Ideally you should have another column/table to make a relation between products and brand, then make the query using the brand and not the name. — Vertig0, Oct 17 '14 at 20:09
it still isn't displaying the total cost of al hp and toshiba products — Jmac88, Oct 17 '14 at 21:13

score 1 · Accepted Answer · answered Oct 17 '14 at 21:28

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)

Mureinik · Answer 2 · 2014-10-17T21:15:10.513

0

Seems like you're looking for something like this:

SELECT SUM(price) AS total_price
FROM   products
WHERE  prod_name LIKE 'HP%' or prod_name LIKE 'Toshiba%';

edited Oct 17 '14 at 21:15

answered Oct 17 '14 at 20:08

Mureinik

2 Answers2