| Author |
Topic |
|
hallyvaux
Starting Member
12 Posts |
 Posted - 2011-05-02 : 19:15:59
|
| I am trying to make something turn out as a whole number under my percentages column. I have the following code but was told I should use commas and dollar signs. The part I am wanting to change is just the statement beginning with ROUND. I want the same result just would like to know how to use , and $. Thank you :) SELECT ORDID "Order ID", NAME, ADDRESS, SELLPRICE "Price Sold", COSTPRICE "Product Cost", ROUND ((COSTPRICE / SELLPRICE) * 100) "%Profit"FROM GS_CUSTOMER, GS_SALESWHERE GS_CUSTOMER.CUSTID = GS_SALES.CUSTIDORDER BY ORDID; |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
hallyvaux
Starting Member
12 Posts |
Posted - 2011-05-02 : 19:27:59
|
| I have an assignment that requires this unless I am totally misinterpreting it. "Research ho to put $ signs and comma editing symbols in numeric results" Generate a report that is titled % Profit for Each Sale and research hot to whos the percent value as a whole number. Research formatting options" Those are my instructions for one problem. I have researched and can't find what I'm looking for so this is my last attempt at research. I've shown the whole numbers by using round and the end result looks like our example. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
hallyvaux
Starting Member
12 Posts |
Posted - 2011-05-02 : 19:38:42
|
| She just wants us to copy a small portion of our output and put it under the script we use to get it. We are using http://lovelace.cs.missouriwestern.edu:5560/isqlplus/ and I am just copying the output underneath to a word document under my scripts. |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-05-02 : 19:39:25
|
| It's a trick question, or poorly worded homework. As soon as you add a character to a number, like $ or ,, it's no longer a number, but a string. So you can't format a number as $100.00 or 1,000, you can only format a string to look like that.select convert(varchar(100),convert(money,1000000),1)(I converted the integer,1000000 to a money data type, and then converted that to stringLook at Books On Line under convert function to see what you get if you change the 1 to a 0 or 2JimEveryday I learn something that somebody else already knew |
|
|
|
hallyvaux
Starting Member
12 Posts |
Posted - 2011-05-02 : 19:40:07
|
| Sorry that was full of nasty grammatical errors. Proofreading before sending is not my forte. |
|
|
|
hallyvaux
Starting Member
12 Posts |
Posted - 2011-05-02 : 19:40:54
|
| With the way things have been, I would go with "poorly worded homework" |
|
|
|
hallyvaux
Starting Member
12 Posts |
Posted - 2011-05-02 : 19:42:09
|
| But for the purposes of a report that I don't intend to do any formatting with, could I use them? If so, how? |
|
|
|
hallyvaux
Starting Member
12 Posts |
Posted - 2011-05-02 : 19:48:40
|
| Ugh. Nvm. I think I see what you are saying. I couldn't use any functions once I convert them into strings so I couldn't get the percentage value. Right? |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-05-02 : 20:15:05
|
| You still can, but now it's a matter of timing! A percentage is a number divided by another number, once you calculate that, then you can make it look like something else (adding $ or % etc.)select '%' + convert(varchar(10), convert(numeric(5,2), 1.0/2 *100 ) )This goes from the inside- out. I converted 1.0/2 (see what happens if you just do 1/2!) to a number with 5 digits, 2 to the left of the decimal, and then converted that to a string so I could add the '%' to it.JimEveryday I learn something that somebody else already knew |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-05-02 : 20:17:01
|
P.S. This is why Tara gave the most correct answer -- do the formatting in the front-end!  JimEveryday I learn something that somebody else already knew |
|
|
|
|
How do I use dollar signs and commas insted?