Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum. |
Notices |
Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
Are you new to LinuxQuestions.org? Visit the following links:
Site Howto |
Site FAQ |
Sitemap |
Register Now
If you have any problems with the registration process or your account login, please contact us. If you need to reset your password, click here.
Having a problem logging in? Please visit this page to clear all LQ-related cookies.
Get a virtual cloud desktop with the Linux distro that you want in less than five minutes with Shells! With over 10 pre-installed distros to choose from, the worry-free installation life is here! Whether you are a digital nomad or just looking for flexibility, Shells can put your Linux machine on the device that you want to use.
Exclusive for LQ members, get up to 45% off per month. Click here for more info.
|
 |
|
08-16-2014, 05:24 AM
|
#1
|
Member
Registered: Mar 2014
Distribution: Fedora 23 - MariaDB 10.1 -
Posts: 130
Rep:
|
how to multiply columns that have dollar signs in them with numbers???
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 
Last edited by cyberdome; 08-16-2014 at 05:27 AM.
|
|
|
08-16-2014, 07:51 AM
|
#2
|
Senior Member
Registered: Apr 2007
Location: West Virginia
Distribution: Ubuntu
Posts: 1,005
|
What type of database is this?
Seems like the salary field is actually a text/varchar field and not a number.....
|
|
|
08-16-2014, 10:10 AM
|
#3
|
Member
Registered: Mar 2014
Distribution: Fedora 23 - MariaDB 10.1 -
Posts: 130
Original Poster
Rep:
|
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?
Last edited by cyberdome; 08-16-2014 at 10:11 AM.
|
|
|
08-16-2014, 10:20 AM
|
#4
|
Senior Member
Registered: Apr 2007
Location: West Virginia
Distribution: Ubuntu
Posts: 1,005
|
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.
|
|
|
08-16-2014, 10:34 AM
|
#5
|
Member
Registered: Mar 2014
Distribution: Fedora 23 - MariaDB 10.1 -
Posts: 130
Original Poster
Rep:
|
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
Last edited by cyberdome; 08-16-2014 at 10:40 AM.
|
|
|
08-16-2014, 11:08 AM
|
#6
|
Senior Member
Registered: Apr 2007
Location: West Virginia
Distribution: Ubuntu
Posts: 1,005
|
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.
|
|
|
08-16-2014, 11:11 AM
|
#7
|
Member
Registered: Mar 2014
Distribution: Fedora 23 - MariaDB 10.1 -
Posts: 130
Original Poster
Rep:
|
refer to screenshot
|
|
|
08-16-2014, 11:16 AM
|
#8
|
Member
Registered: Mar 2014
Distribution: Fedora 23 - MariaDB 10.1 -
Posts: 130
Original Poster
Rep:
|
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.
|
|
|
08-16-2014, 01:24 PM
|
#9
|
Senior Member
Registered: Apr 2007
Location: West Virginia
Distribution: Ubuntu
Posts: 1,005
|
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.
|
|
|
08-16-2014, 04:01 PM
|
#10
|
LQ Guru
Registered: Mar 2004
Distribution: Slackware
Posts: 6,795
|
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)
|
|
|
08-16-2014, 04:30 PM
|
#11
|
Senior Member
Registered: Apr 2007
Location: West Virginia
Distribution: Ubuntu
Posts: 1,005
|
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.
|
|
|
08-16-2014, 06:12 PM
|
#12
|
Member
Registered: Mar 2014
Distribution: Fedora 23 - MariaDB 10.1 -
Posts: 130
Original Poster
Rep:
|
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?
Last edited by cyberdome; 08-16-2014 at 06:19 PM.
|
|
|
08-16-2014, 06:30 PM
|
#13
|
Member
Registered: Mar 2014
Distribution: Fedora 23 - MariaDB 10.1 -
Posts: 130
Original Poster
Rep:
|
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.
Last edited by cyberdome; 08-16-2014 at 09:07 PM.
|
|
|
08-16-2014, 07:47 PM
|
#14
|
LQ Guru
Registered: Mar 2004
Distribution: Slackware
Posts: 6,795
|
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
Last edited by keefaz; 08-16-2014 at 07:55 PM.
Reason: Initially I used TRUNCATE but ROUND is better for accuracy
|
|
|
08-16-2014, 08:41 PM
|
#15
|
Senior Member
Registered: Apr 2007
Location: West Virginia
Distribution: Ubuntu
Posts: 1,005
|
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.
|
|
|
All times are GMT -5. The time now is 08:04 AM.
|
LinuxQuestions.org is looking for people interested in writing
Editorials, Articles, Reviews, and more. If you'd like to contribute
content, let us know.
|
Latest Threads
LQ News
|
|