[SOLVED] how to multiply columns that have dollar signs in them with numbers???

cyberdome · 08-16-2014, 04:24 AM

I have a table with salary amount that I want to multiply with .08 but when I run my SQL query, It returns a 0 zero values. Because there is a dollar sign in every row in my Column salary.

SELECT SALARY * .08 AS INCOME_TAX FROM table_name;

It works on my other tables that don't have the dollar sign in the rows.

But I think the dollar sign is causing a issue and displaying Zero,

Anyone know how to execute a SQL query with dollar signs so that I can multiply .08 and display the values?

thanks in advance

jkirchner · 08-16-2014, 06:51 AM

What type of database is this?

Seems like the salary field is actually a text/varchar field and not a number.....

cyberdome · 08-16-2014, 09:10 AM

Quote:

Originally Posted by jkirchner

What type of database is this?

Seems like the salary field is actually a text/varchar field and not a number.....

YES, the column SCHEMA is VARCHAR.

This is a MySQL Database. Using phpMyAdmin.

Will the SQL Query not work on VARCHAR?

jkirchner · 08-16-2014, 09:20 AM

You have to convert the text field to a number. Normally one would store dollar amounts as a double/float and worry about displaying the $ sign on a report or such. So, best way to store the data would be just raw 23564.58 for example.

This site shows some techniques to convert the field.

An option is to use substr on the field and parse the $ signs away in the query and covert the new field using methods found here

Is this a big database or something you are learning on? If learning and database is small you can just recreate it using a float for the salary. The dressing up and "pretty-fying" of it can all be done in the report end where you can add the $ signs and any other formatting you want.

cyberdome · 08-16-2014, 09:34 AM

Quote:

Originally Posted by jkirchner

You have to convert the text field to a number. Normally one would store dollar amounts as a double/float and worry about displaying the $ sign on a report or such. So, best way to store the data would be just raw 23564.58 for example.

This site shows some techniques to convert the field.

An option is to use substr on the field and parse the $ signs away in the query and covert the new field using methods found here

Is this a big database or something you are learning on? If learning and database is small you can just recreate it using a float for the salary. The dressing up and "pretty-fying" of it can all be done in the report end where you can add the $ signs and any other formatting you want.

This specific table has 1.1 million rows/records. And I created the data from Mockaroo.com. Mockaroo unfortunately assigns it as VARCHAR.

I ran this SQL Query,

SELECT SALARY,CONVERT(SUBSTRING_INDEX(SALARY * .08,'-',-1),UNSIGNED INTEGER) AS num
FROM table_name;

my table_name is bank_of_america_payroll

first row in SALARY has the value, $96529.34

jkirchner · 08-16-2014, 10:08 AM

I went to the site quickly, did you generate the table yourself? If so, you can select the field to be a number, it is under the basic tab. Is this test data you are playing with then? If it is, to make things easier I would set it up right from the start using a number and not varchar. I design and work with databases at work. I always followed this rule: If you are going to do math on it, make it a number, if it is a number (like an id number or such) but you are not doing math on it, then make it text/varchar.

cyberdome · 08-16-2014, 10:11 AM

refer to screenshot

cyberdome · 08-16-2014, 10:16 AM

Quote:

Originally Posted by jkirchner

I went to the site quickly, did you generate the table yourself? If so, you can select the field to be a number, it is under the basic tab. Is this test data you are playing with then? If it is, to make things easier I would set it up right from the start using a number and not varchar. I design and work with databases at work. I always followed this rule: If you are going to do math on it, make it a number, if it is a number (like an id number or such) but you are not doing math on it, then make it text/varchar.

YES, this is all junk data I am playing with. I am trying to learn databases and SQL queries. I attached a screenshot on previous post. Just curious how would the convert SQL query would work?

But your post makes made lot of sense about creating databases from scratch. If playing with calculations, mathematics, then create SCHEMA as INT, BIGINT.

If not playing with calculations, matchematics, then create SCHEMA as VARCHAR.

It does make sense.

jkirchner · 08-16-2014, 12:24 PM

You can cheat this but using a substr() on the field with the dollar signs, First I would check the lengths of the string in salary column just to see if you are lucky and they are the same length. If the lengths are different you would have to add case statements too, to the examples below.

Assuming they are all length of 9 (count the . too) then substr(salary,2,8) should do it. Then take that and convert it to numbers (I am not sure about mysql but for me to_number works in my oracle stuff at work, check the mysql link I had above for syntax). You can do all this in the query and multiply as well.

keefaz · 08-16-2014, 03:01 PM

Maybe use SUBSTRING, then CAST to DECIMAL (for monetary number)

Code:

MariaDB [(none)]> select CAST(SUBSTRING("$96529.34" FROM 2) AS DECIMAL(10,2)) * 0.8;
+------------------------------------------------------------+
| CAST(SUBSTRING("$96529.34" FROM 2) AS DECIMAL(10,2)) * 0.8 |
+------------------------------------------------------------+
|                                                  77223.472 |
+------------------------------------------------------------+
1 row in set (0.00 sec)

jkirchner · 08-16-2014, 03:30 PM

Nice keefaz! That is what I was trying to explain but I do not know mysql syntax so well; I use mostly oracle and MS Access at work.

cyberdome · 08-16-2014, 05:12 PM

Quote:

Originally Posted by keefaz

Maybe use SUBSTRING, then CAST to DECIMAL (for monetary number)

Code:

MariaDB [(none)]> select CAST(SUBSTRING("$96529.34" FROM 2) AS DECIMAL(10,2)) * 0.8;
+------------------------------------------------------------+
| CAST(SUBSTRING("$96529.34" FROM 2) AS DECIMAL(10,2)) * 0.8 |
+------------------------------------------------------------+
|                                                  77223.472 |
+------------------------------------------------------------+
1 row in set (0.00 sec)

WOW! very nice, Thanks so much. Is there a way to execute the SQL query for all the records/ROWS?

cyberdome · 08-16-2014, 05:30 PM

Quote:

Originally Posted by jkirchner

Nice keefaz! That is what I was trying to explain but I do not know mysql syntax so well; I use mostly oracle and MS Access at work.

Yes, Keefaz gave the correct SQL query. But I agree with you about creating the correct SCHEMA for mathematical calculations. Because, the way I have setup would be a very difficult effort. I suppose your method is how it is done in the real world. For example, let us say we want to display SALARY, when total SALARY is 1500 and you want to show 1500 * .08 AS INCOME_TAX and show the NET_INCOME. So, below is the SQL query

Quote:

SELECT SALARY * .08 AS INCOME_TAX, SALARY - SALARY * .08 AS NET_INCOME;

That was my end goal.

keefaz · 08-16-2014, 06:47 PM

Code:

SELECT 
@salary:=CAST(SUBSTRING(salary FROM 2) AS DECIMAL(10,2)) AS SALARY,
ROUND((@salary * 0.8),2) AS INCOME_TAX,
ROUND((@salary - @salary * 0.8),2) AS NET_INCOME
FROM bank_of_america_payroll;

I used ROUND to display only 2 decimal places

jkirchner · 08-16-2014, 07:41 PM

Quote:

Originally Posted by cyberdome

Yes, Keefaz gave the correct SQL query. But I agree with you about creating the correct SCHEMA for mathematical calculations. Because, the way I have setup would be a very difficult effort. I suppose that is how it is done in the real world. For example, let us say we want to display SALARY, when total SALARY is 1500 and you want to show 1500 * .08 AS INCOME_TAX and show the NET_INCOME. So, below is the SQL query

That was my end goal.

Correct

I always try to think what I want to get out of the databases so I can best figure out how to set them up. That rule of make the fields numbers if you ever want to do math with them is a good one. Saves extra effort later.