Simple division is not returning actual value

Hi,

I know I'm making a mistake somewhere, just not sure where.

I'm dividing 67/14 and want to get the result as 4.78 -- I'd be OK with 4.79 
which is the result I'd get if the last digit was rounded up.

When I do
SELECT CAST((67/14) AS DECIMAL(3,2))

I'm getting 4.00. What am I doing wrong?
-- 
Thanks,

Sam
0
Utf
4/27/2010 3:23:01 PM
sqlserver.programming 1873 articles. 0 followers. Follow

3 Replies
1216 Views

Similar Articles

[PageSpeed] 53

This is because of integer division. Try this:

SELECT 67/CAST(14 AS DECIMAL(5,2)) AS a,
        67/14.0 AS b,
        1.0 * 67/14 AS c,
        CAST(1.0 * 67/14 AS DECIMAL(3, 2)) AS d,
        ROUND(1.0 * 67/14, 2, 1) AS e;

/*

a         b         c         d     e
--------- --------- --------- ----- ----------
4.785714  4.785714  4.785714  4.79  4.780000


*/

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
4/27/2010 4:02:22 PM
Try this:

SELECT ROUND(CAST(67 AS DECIMAL) / CAST(14 AS DECIMAL),2)

You can get rid of the ROUND() operator if you want, or change the '2'
at the end to change the rounding precision.

Thanks,

Bill

0
bill
4/27/2010 4:13:54 PM
Thank you both very much :-)
-- 
Thanks,

Sam


"Sam" wrote:

> Hi,
> 
> I know I'm making a mistake somewhere, just not sure where.
> 
> I'm dividing 67/14 and want to get the result as 4.78 -- I'd be OK with 4.79 
> which is the result I'd get if the last digit was rounded up.
> 
> When I do
> SELECT CAST((67/14) AS DECIMAL(3,2))
> 
> I'm getting 4.00. What am I doing wrong?
> -- 
> Thanks,
> 
> Sam
0
Utf
4/27/2010 5:01:01 PM
Reply:

Similar Artilces:

field returns a value if at least one matching record is found
I had posted this question in the Forms thread, but that was probably the wrong place. TblPartNumInfo TblDefectRpt My query for PartNumInfo brings together lots of different tables including customers, contact info, status of jobs. A feature I need to add is a warning if at least one defect report exists for a given part number. It would be shown on the form as a conditional format of the text box where the part number is shown. So I would like to add a field to my query that would return some value (like the part#ID) if at least one Defect Report exists for a part#. I created a sepa...

Hard Return in Cell
Can you put a hard return in a cell. Probably basic, but can't figure it out. Hi Alt Enter -- HTH. Best wishes Harald Followup to newsgroup only please. "Monica" <mbristow@adtron.com> wrote in message news:2ab8501c3927c$17cd5b00$a601280a@phx.gbl... > Can you put a hard return in a cell. > Probably basic, but can't figure it out. Monica Yes: hold down the Alt key and press Enter. However, you might like to follow the exchange in the posts just below titled "Excel not filtering as expected" in case that turns out to have any implications for what y...

Simple graph
I have a chart with zip codes and a population rate. ZIP RATE 80001 2.34 80002 1.23 80003 1.23 I want to make a chart that has the rate on the x axis and the number of times that rate occurs on the y axis. | | | * | | * |_________________________________ | | 1.23 2.34 Thank you. I will assume the ZIP and RATE stuff is in A1:B200 (labels in row 1) Label in G1 to read RATE (but leave it empty for now), label in H2 to read COUNT Make a list of rates in G2:G20 (say) In H2 =COUNTIF($B$2:$B$200,G2) Copy down the row Select H1:G20 and make a...

Return a value if criteria contained in cell
I would like to return a value if criteria is met. If A2 contains the word "apples", then in cell B2, write "apples". If A2 contains the word "oranges, then in cell B2, write "oranges". If A2 does not contain the words "apples" or "oranges", leave the cell blank. Can you help me with a formula for this? Thanks in advance. IF(OR(A2="apples",A2="oranges"),A2,"") "jhicsupt" wrote: > I would like to return a value if criteria is met. > > If A2 contains the word "apples", then in...

Returned Emails
I am receiving returned emails saying that recipient can't be found (example below). I'm not sending these emails. I found some older postings saying that the only thing that can be done is to filter the bounce back emails. I'm hoping that there might be something that will eliminate them from being sent. I'm using Outlook 2003 SP1. Any help would be appreciated. ----Example---- The original message was received at Thu, 10 Feb 2005 08:08:24 -0500 (EST) from kathy.mindspring.com [64.94.55.196] *** ATTENTION *** Your e-mail is being returned to you because there was a pr...

Simple help with implementing Outlook-like GUI
Hi All. I am an experienced unix programmer who sometimes has to do something in the windows world, and always has newbie-questions. Basically I want to write an application program that looks like outlook in that it has nice icons down the left hand side that choose the content of the main area in the right hand side. So I started the MFC App wizard, and got me a SDI program with a CLeftFrame (CTreeView) and a CMainFrame (CFrameWnd). Then I prepared the following snippet of code to draw the buttons: // Create a pushbutton CBitmapButton* pmyButton; pmyButton = new CB...

Simple Simple Simple
Just started Excel (again) and need to make an easy speadsheet fo calculating square inches. Column A: Height Column B: Legnth Column C: Total Tags per 16 x 24 Column D: Cost per tag Figures I know: each sheet cost me $9.98. each sheet is 16 x 24 inches What I want to do is enter the Height, enter the Length and have th total (sum) be entered into columns C and D. I can't believe I forgot how to enter formulas. Any help will b appreciated. Thank you, Crai -- Message posted from http://www.ExcelForum.com Hi Craig, One Way, might be better ways but this'll work: List the colou...

Incorrect Actual Amounts in Project Tracking
I am using version 7.5 When I am running reports in reports-project-projects in progress cost category I am getting incorrect actual total costs amounts and in the earnings column there are amounts that are added on to the actual costs. I don't know why it would be doing this? Any help would be greatly appreciated! Thanks ...

IRS Tax Return: Category?
Does anyone know which category needs to be used for a direct deposit from the IRS? As a tax return, how do I record it properly, so that my tax estimation for this year does not classify this as being tabable income? In microsoft.public.money, James wrote: >Does anyone know which category needs to be used for a >direct deposit from the IRS? As a tax return, how do I >record it properly, so that my tax estimation for this >year does not classify this as being tabable income? I use Tax Refund:Fed. You can create whatever category you like. "James" <anonym...

#VALUE! Error #2
Why does the following formula give me a #VALUE! error? =IF(AND(OR(E7="E-120-D",E8="E-120-D",E9="E-120-D",E10="E-120-D",E11="E-120-D",E12="E-120-D",E13="E-120-D",E14="E-120-D",E15="E-120-D",E16="E-120-D"),OR("38X12","40X12","42X12","44X12","46X12","48X12","50X12","52X12","54X12","56X12","58X12","60X12","62X12","64X12","66X12","68X12",&qu...

Simple hack to get $500 to your home. 06-05-10
Simple hack to get $500 to your home at http://uknews.tk Due to high security risks,i have hidden the cheque link in an image. in that website on left side below search box, click on image and enter your name and address where you want to receive your cheque.please dont tell to anyone. ...

Simple Query Wizard hangs
I am using Access 2003 When I initiate the "Simple Query Wizard", I am presented with the first screen - "What fields do you want in your query" When I click on the pulldown list to select a table, I get the following message: "The expression On Get Focus you entered as the event property setting produced the following error: the text you entered isn't an item in the list * the expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]. * There may have been an error evaluating the function, event, or macro&q...

CHANGE Y AXIS Value
I need to change the value on the Y Axis to inches but cannot figure out the right way to go. Thanks, Cindy Hi, The scaling is not really done in inches or centimeters or any measurement system. It's purely a numeric scale. Cheers Andy Cindy wrote: > I need to change the value on the Y Axis to inches but cannot figure out the > right way to go. > > Thanks, > Cindy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

Returning a value froma dialog
Hi, I have an app that pops-up a dialog to ask for the username / password. When the user clicks the login button in the dialog, my CLogin class (derived from CDialog) attempts to login the user, if it suceeds, i need to return OK and close the Dlg. If it fails, i need to return the error code (-1, -3 etc..) in my app: CLogin loginDlg; int retCode = loginDlg.DoModal(); if(retCode != LOGIN_OK) { ... } How do i make my dialog return a value? CDialog::EndDialog( int nResult ); "JoeB" <joe____A_A_A_A_A_ANoSpamPleaseThankyou_@kybert.com> wrote in message news:Ok$...

Returning A Row & Column from a Table array
Hi, I have a table as follows: Cells A2:A150 List of Names Cells B1:CX1: List of Duties The Array, Cells B2:CX150 have Dates is them many are duplicated or triplicated I have Created Conditional Formats for the Array as follows: Cell Value <NOW(0) is Red Cell Value <NOW(0) +14 is Yellow Cell Value <NOW(0) + 42 is Orange What I would like to achieve now, perhaps in a Separate Worksheet, when I open the workbook daily, 3 Tables indicating the Names & Duties which are now RED, Orange and Yellow. I tried to use INDEX & MATCH but confusing myself. Does this require a VL...

I Need a Simple Sales Software
Hello, I am looking for software to keep track of my prospects, customers, and the notes of the sales process. I spend about one hour a day on sales and marketing, so I prefer a simple and inexpensive software. Maximizer, Outlook, Act, and Goldmine seem made for the full-time professional salesperson. Please let me know if what I am looking for exists. Best Regards, T.I. ...

CRM custom report with filter CRMAF returns no data
Hi guys, I have uploaded 2 reports, 1 with CRMAF and 1 without. The one without CRMAF returns data but the one with CRMAF returns no data. The query is exactly the same. I am not sure what the problem is. Can someone please advise? Thanks. Regards, Eunice Hi Eunice - Could you please post the syntax of your query. Love to help... Mike. "Eunice" wrote: > Hi guys, > > I have uploaded 2 reports, 1 with CRMAF and 1 without. The one without CRMAF > returns data but the one with CRMAF returns no data. The query is exactly the > same. I am not sure what the probl...

historical inventory (Item Value List)
Is there any way to get RMS to display the Item Value List on previous dates? I would like to be able to view historical inventory category values. TIA ______________________________________________________ Larry Leveen OlyBikes Bikes, Parts, Repairs & GREAT Customer Service! 124 State Avenue NE Olympia, WA 98501 P: 360-753-7525 F: 360-528-7526 info@olybikes.com www.olybikes.com Featuring free bike advocacy and safety materials! I actually just contacted Microsoft about this myself for a client of mine. They informed me that the inventory lists are real-time only, that there i...

Add values with same code
I have a ws with two columns one with a value and other with a code, want to add all the values with the same code, any ideas???? value code 14 1 21 2 14 1 16 1 21 2 Result for code 1=44 Result for code 2=42 I'll have about 10 different codes. Thank you Rodolf -- Message posted from http://www.ExcelForum.com Have a look in HELP index for SUMIF -- Don Guillett SalesAid Software donaldb@281.com "rodolform >" <<rodolform.15jewe@excelforum-nospam.com> wrote in message news:rodolform.15jewe@excelf...

Lookup Field Value
Hi Everboby I got a lookup field and I want to know that which value is selected. I know it is very basic but I cannot able to figure it out. Any help?????????? Thanks in advance. -- Jaber Hi you can try this var lookupItem = new Array; lookupItem = crmForm.all.new_productid.DataValue; // lookup field if(lookupItem[0] != null) { //productid GUID alert(lookupItem[0].id); //product name alert( lookupItem[0].name); } "Jaber" wrote: > Hi Everboby > I got a lookup field and I want to know that which value is selected. > I know it is very basic but I can...

Make it more simple or intuitive to do simple things
I appreciate the fact that applications are becoming more versatile and able to do things that we hardly thought possible in the past but I feel that in this added complexity you are losing sight of the need to do simple things easily without resorting to trial and error or consulting "help" which often anyhow doesn't lead one straight to the solution! An example is how to produce a chart with a series of months i.e Jan Feb Mar etc appearing on the X axis. This is no doubt something that resulted naturally in the first versions of Excel charts or in a competitor's ea...

Simple public folder permission problem
I have a public task list folder. However even though I have given everyone "Author" priviledges and full control of the directory, users cannot update the tasks (although they can create new ones). The changes to tasks get reset to their original values. Where should I look to fix this problem? Brian How did you grant Author Access, via Outlook or ESM? What do you meanyou gave them full control of the directory? "Brian Taylor" <taylorb@newsgroups.nospam> wrote in message news:e2hwumGrEHA.1160@tk2msftngp13.phx.gbl... > I have a public task list folder. Ho...

How suppress chart points
I want to suppress chart points for a cumulative line graph of monthly telephone costs where the particular month's values have not yet been entered but there is a formula in the cell which currently returns null but will return the value once it is posted on the accountmaster sheet. Hi, Use NA(). The will cause the data marker to be omitted. Note the line will be interpolated between valid data points. Something like this, =IF( <test> , <value> , NA() ) Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "sarcastix" <sarcastix@di...

Can an If function return a truely blank value?
Hi everyone, In columns A and B on my worksheet I input numbers from a printout. If there are any 0 values on the printout for the data to be entered in either column A or B, then the cell should be left empty rather than the 0 input. In column C I had a formula that divides the value in column A by the value in column B e.g. =A1/B1. For the rows with blank cells, the formula in column C returned either 0 or #DIV/0! where I wanted it to be blank. To get around this problem I changed the formula in column C e.g: =IF(OR(ISBLANK(A1),ISBLANK(B1)),"",A1/B1) This works fine as far a...

What is simple text?
When creating a custom list that includes a column of accounting formated data, I get an error message that 'fields without simple text will be ignored'. The result is all other data except the accounting formatted data. Help!!!! I suppose it means without the currency symbol and thousands delimiters. On Fri, 8 Apr 2005 11:01:04 -0700, TexMas <TexMas@discussions.microsoft.com> wrote: >When creating a custom list that includes a column of accounting formated >data, I get an error message that 'fields without simple text will be >ignored'. The result is ...