LinuxQuestions.org
Help answer threads with 0 replies.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
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


Reply
  Search this Thread
Old 08-16-2014, 05:24 AM   #1
cyberdome
Member
 
Registered: Mar 2014
Distribution: Fedora 23 - MariaDB 10.1 -
Posts: 130
Blog Entries: 2

Rep: Reputation: 8
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.
 
Old 08-16-2014, 07:51 AM   #2
jkirchner
Member
 
Registered: Apr 2007
Location: West Virginia
Distribution: Linux Mint
Posts: 936

Rep: Reputation: 293Reputation: 293Reputation: 293
What type of database is this?

Seems like the salary field is actually a text/varchar field and not a number.....
 
Old 08-16-2014, 10:10 AM   #3
cyberdome
Member
 
Registered: Mar 2014
Distribution: Fedora 23 - MariaDB 10.1 -
Posts: 130

Original Poster
Blog Entries: 2

Rep: Reputation: 8
Quote:
Originally Posted by jkirchner View Post
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.
 
Old 08-16-2014, 10:20 AM   #4
jkirchner
Member
 
Registered: Apr 2007
Location: West Virginia
Distribution: Linux Mint
Posts: 936

Rep: Reputation: 293Reputation: 293Reputation: 293
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.
 
Old 08-16-2014, 10:34 AM   #5
cyberdome
Member
 
Registered: Mar 2014
Distribution: Fedora 23 - MariaDB 10.1 -
Posts: 130

Original Poster
Blog Entries: 2

Rep: Reputation: 8
Quote:
Originally Posted by jkirchner View Post
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.
 
Old 08-16-2014, 11:08 AM   #6
jkirchner
Member
 
Registered: Apr 2007
Location: West Virginia
Distribution: Linux Mint
Posts: 936

Rep: Reputation: 293Reputation: 293Reputation: 293
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.
 
Old 08-16-2014, 11:11 AM   #7
cyberdome
Member
 
Registered: Mar 2014
Distribution: Fedora 23 - MariaDB 10.1 -
Posts: 130

Original Poster
Blog Entries: 2

Rep: Reputation: 8
refer to screenshot
Attached Thumbnails
Click image for larger version

Name:	sql_query_m1.png
Views:	22
Size:	158.6 KB
ID:	16142   Click image for larger version

Name:	sql_queery_m2.png
Views:	17
Size:	148.0 KB
ID:	16143  
 
Old 08-16-2014, 11:16 AM   #8
cyberdome
Member
 
Registered: Mar 2014
Distribution: Fedora 23 - MariaDB 10.1 -
Posts: 130

Original Poster
Blog Entries: 2

Rep: Reputation: 8
Quote:
Originally Posted by jkirchner View Post
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.
 
Old 08-16-2014, 01:24 PM   #9
jkirchner
Member
 
Registered: Apr 2007
Location: West Virginia
Distribution: Linux Mint
Posts: 936

Rep: Reputation: 293Reputation: 293Reputation: 293
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.
 
Old 08-16-2014, 04:01 PM   #10
keefaz
LQ Guru
 
Registered: Mar 2004
Distribution: Slackware
Posts: 6,552

Rep: Reputation: 872Reputation: 872Reputation: 872Reputation: 872Reputation: 872Reputation: 872Reputation: 872
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)
 
Old 08-16-2014, 04:30 PM   #11
jkirchner
Member
 
Registered: Apr 2007
Location: West Virginia
Distribution: Linux Mint
Posts: 936

Rep: Reputation: 293Reputation: 293Reputation: 293
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.
 
Old 08-16-2014, 06:12 PM   #12
cyberdome
Member
 
Registered: Mar 2014
Distribution: Fedora 23 - MariaDB 10.1 -
Posts: 130

Original Poster
Blog Entries: 2

Rep: Reputation: 8
Quote:
Originally Posted by keefaz View Post
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.
 
Old 08-16-2014, 06:30 PM   #13
cyberdome
Member
 
Registered: Mar 2014
Distribution: Fedora 23 - MariaDB 10.1 -
Posts: 130

Original Poster
Blog Entries: 2

Rep: Reputation: 8
Quote:
Originally Posted by jkirchner View Post
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.
 
Old 08-16-2014, 07:47 PM   #14
keefaz
LQ Guru
 
Registered: Mar 2004
Distribution: Slackware
Posts: 6,552

Rep: Reputation: 872Reputation: 872Reputation: 872Reputation: 872Reputation: 872Reputation: 872Reputation: 872
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
 
Old 08-16-2014, 08:41 PM   #15
jkirchner
Member
 
Registered: Apr 2007
Location: West Virginia
Distribution: Linux Mint
Posts: 936

Rep: Reputation: 293Reputation: 293Reputation: 293
Quote:
Originally Posted by cyberdome View Post
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.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Usage of /dev/null minus signs instead of greater-than signs keif Programming 6 10-20-2013 01:41 PM
How to multiply columns and add them from a file? Niteawk Linux - Newbie 13 02-19-2013 02:51 AM
multiply two numbers in Linux script Ghazale Linux - Newbie 5 10-12-2011 10:06 PM
Replacing selected columns by Serial numbers incremently raghu123 Programming 8 08-25-2008 03:27 AM
Doing arithmetics on columns of numbers hhegab Programming 2 03-08-2006 11:27 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Software

All times are GMT -5. The time now is 11:39 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration