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

Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
Rachid Bahman
  • 1

2 Answers2

1

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 column amount instead 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)
Community
  • 1
  • 1
Sebastian Brosch
  • 42,106
  • 15
  • 72
  • 87
  • OMG :-) even if this will work, we can't accept that as an answer on SO :-) that is very wrong path to go – Alex Apr 21 '17 at 14:36
  • Sure that stands to work. However, the use of multiple functions could have been and should be avoided. [***This....***](http://stackoverflow.com/questions/43545216/how-do-i-count-values-in-columns-of-a-table#comment74142868_43545216) is what they really should do or have done in the first place, before starting their project. – Funk Forty Niner Apr 21 '17 at 14:38
  • @Alex - you are right. I added some steps for improving the table. I saw you answer to late... – Sebastian Brosch Apr 21 '17 at 14:52
1

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

http://sqlfiddle.com/#!9/2c335cc/1

Alex
  • 16,739
  • 1
  • 28
  • 51
  • Agreed, yet I've asked for clarification in comments and have yet to hear from [this one I left...](http://stackoverflow.com/questions/43545216/how-do-i-count-values-in-columns-of-a-table#comment74143239_43545216). IMHO, the question's unclear as to *what* they want to count here, should `COUNT()` need to be used. All the more reasons why I didn't submit an answer of my own. – Funk Forty Niner Apr 21 '17 at 14:48
  • I guess OP meant *calculate* but not the `COUNT` function. It is just about terms. for any DBA *count* means `COUNT()` but in English it could mean kind of *calculate* :-) – Alex Apr 21 '17 at 14:53
  • I guess: `total amount = SUM(amount)` – Sebastian Brosch Apr 21 '17 at 14:55
  • Yes, there could be a language barrier. OP placed a comment under their question but I don't know what they meant by that. Maybe just the `amount` column. OP: *"i want to count the total amount $$ of this commande"* @Alex – Funk Forty Niner Apr 21 '17 at 14:56
  • @Fred-ii- exactly I want to count just the amount column – Rachid Bahman Apr 21 '17 at 14:58
  • Please use `int` or `decimal` instead of `double` for amounts. See also http://stackoverflow.com/a/1165788/854080 – Daan Wilmer Apr 21 '17 at 15:01
  • @RachidBahman Count as in and seeing you have 4 rows, to show there are 4 rows or the total sum of and for example: `1.00$` times 4 rows being `4.00$`? – Funk Forty Niner Apr 21 '17 at 15:01
  • @Alex Just to let you know that I'm not planning on submitting an answer to overtake yours; just trying to help out here :-) I think y'all can take over from here; see what the OP writes in response to my above comment to them. – Funk Forty Niner Apr 21 '17 at 15:05