id | produit | qte | amount ---------------------------- 1 | iphone | 15 | 110$ 2 | tablet | 18 | 50$ 3 | laptop | 19 | 250.99$ 4 | Tide | 21 | 10$
How can I count the Total amount of all this produit in this table ?
You can use the following query using SUM and REPLACE:
SELECT SUM(CAST(REPLACE(amount, '$', '') AS DECIMAL)
FROM table_name
I recommend you to remove the
$from columnamountinstead of using a query like the above one! The script / frontend working with these values should know which currency the column is using.
How you can improve your database design / column?
Remove all the $ from this column using the following query:
UPDATE table_name SET amount = TRIM(REPLACE(amount, '$', ''))
Execute the following query to get all non numeric values:
SELECT * FROM table_name WHERE NOT CONCAT('', amount * 1) = amount
If the query above don't return any rows you can ALTER your table to change the column type to DECIMAL(6, 2) or DOUBLE (There can be floating numbers?):
ALTER TABLE table_name MODIFY COLUMN amount DECIMAL(6, 2)
You need to change your table structure to store your amount as DOUBLE but not VARCHAR
UPDATE t SET amount = REPLACE(amount, '$','');
ALTER TABLE t MODIFY amount DOUBLE;
and then you can use aggregated function SUM:
SELECT SUM(amount) FROM t