How to add columns values with suffixed currency sign in MySQL

MySQLMySQLi Database

For this, you can use aggregate function SUM(). Let us first create a table:

mysql> create table DemoTable616 (Price varchar(100));
Query OK, 0 rows affected (0.59 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable616 values('€200.00');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable616 values('€300.00');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable616 values('€500.00');
Query OK, 1 row affected (0.12 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable616;

This will produce the following output −

+-----------+
| Price     |
+-----------+
| €200.00   |
| €300.00   |
| €500.00   |
+-----------+
3 rows in set (0.00 sec)

Here is the query to add column values. Within the sum() method, we have replaced the currency sign, so that we can easily add the column values −

.mysql> select sum(replace(Price, '€', '')) as Total_Price from DemoTable616;

This will produce the following output −

+-------------+
| Total_Price |
+-------------+
| 1000        |
+-------------+
1 row in set (0.00 sec)
raja
Published on 23-Aug-2019 09:45:11
Advertisements