SQL Server Division With No Decimal

Aug 20, 2004

Hi - I have what seems like a simple problem and I cannot find an answer to it. I'm hoping someone here can help.

I want to divide 2 numbers but have the answer come back with no decimal places. I don't want to round up or down..just give me the whole number and drop the decimal places. For example:

Select units/60 as answer


And units = 280. I want the "answer" to be 4 and NOT 4.66.

Any ideas?

I'm on SQL server version 8.0.

Thanks in advance!
Caitlin

View 1 Replies


ADVERTISEMENT

Decimal Division

Mar 7, 2005

Hi,

I'm trying to divide two decimal(19,4) numbers but it keeps giving me a Divide by 0 error.

- 10698.25 / 76782.11 = -0.13 (but I get a Divide by 0)

I looked it up in the Sql Server docs, does the Divide by 0 error also cover Stack overflows?

If so, how do I get around this?

Many thanks,
Stuart

View 1 Replies View Related

Decimal And Division

Mar 5, 2008

Could anyone explain the difference between the following 3 queries?

select CONVERT(decimal(38,25),100) / CONVERT(decimal(38,25),1750)

select CONVERT(decimal(19,8),100) / CONVERT(decimal(19,8),1750)

select (100.000/ 1750.000).

I would expect the first one to give me the most accurate result but the second query with a lower precision and scale returns a result with a greater scale.Thanx.

View 3 Replies View Related

SQL 2012 :: Convert Division Of Int Result Into Decimal

Jul 4, 2015

Which one is good method to convert a following division into 2 decimal digits.

q1)

select cast(( cast( sum(population) as decimal) * 100) / sum(totalpopulation) as decimal(7,2)
from
table1 group by state

q2)

select cast(( sum(population) * cast( 100 as decimal)) / sum(totalpopulation) as decimal(7,2)
from
table1 group by state

q3)

select cast( (sum(population) * 100) / cast( sum(totalpopulation) as decimal) as decimal(7,2)
from
table1 group by state

q4)

select cast(( cast(sum(population) as decimal) * 100) / cast( sum(totalpopulation) as decimal) as decimal(7,2)
from
table1 group by state

q5)

select cast( (cast(sum(population) as decimal) * cast(100 as decimal) ) / cast( sum(totalpopulation) as decimal) as decimal(7,2)
from
table1 group by state

q6)

select cast(( cast(sum(population) * 100 as decimal) ) / cast( sum(totalpopulation) as decimal) as decimal(7,2)
from
table1 group by state

View 5 Replies View Related

Report Server: Division Between 2 Subtotal, Can This Be Done?

Feb 1, 2007

I have a table that have the following fields:
Outlet,EmployeeNumber,WorkingDate,WorkingHour,Ince ntive

when I put it into a matrix report, the Outlet is assiged to Page, the EmployeeNumber is assgined to ROW, the WorkingDate is assigned to Column and both WorkingHour & Incentive is assigned to Data(display in row but not column). There is a subtotal at the extreme right side.

May I know how can I squeeze in another element display at the report showing sum of Incentive / sum of WorkingHour? This new element shall be placed at each row as it's indicating the average incentive of each employee.

Thanks! :)

View 2 Replies View Related

Legacy Database Uses Decimal Data Types.--&> AutomobileTypeId (PK, Decimal(10,0), Not Null) Why Not Integers Instead ?

Sep 26, 2007

I am working with a legacy SQL server database from SQL Server 2000. I noticed that in some places that they use decimal data types, that I would normally think they should be using integer data types. Why is this does anyone know?
 
Example: AutomobileTypeId (PK, decimal(10,0), not null)

View 5 Replies View Related

Data Type With Decimal Point For Decimal Values But Not For Whole Integers

Dec 8, 2013

I am creating a table on SQL Server. One of the columns in this new table contains whole integer as wells as decimal values (i.e. 4500 0.9876). I currently have this column defined as Decimal(12,4). This adds 4 digits after the decimal point to the whole integers. Is there a data type that will have the decimal point only for decimal values and no decimal point for the whole integers?

View 2 Replies View Related

Cast Or Convert Nvarchar With Comma As Decimal Separator To Decimal

Apr 29, 2008

Hello.

My database stores the decimals in Spanish format; "," (comma) as decimal separator.

I need to convert decimal nvarchar values (with comma as decimal separator) as a decimal or int.


Any Case using CAST or CONVERT, For Decimal or Int gives me the following error:

Error converting data type varchar to numeric



Any knows how to resolve.

Or any knows any parameter or similar, to indicate to the Cast or Convert, that the decimal separator is a comma instead a dot.

View 5 Replies View Related

Converting (casting) From Decimal(24,4) To Decimal(21,4) Data Type Problem

Jul 24, 2006

Hello!



I would like to cast (convert) data type decimal(24,4) to
decimal(21,4). I could not do this using standard casting function
CAST(@variable as decimal(21,4)) or CONVERT(decimal(21,4),@variable)
because of the following error: "Arithmetic overflow error converting
numeric to data type numeric." Is that because of possible loss of the
value?

Thanks for giving me any advice,

Ziga

View 6 Replies View Related

How Can I Use The Decimal Comma Instead Of Decimal Point For Numbers In Jet Engine?

Sep 19, 2007



I wanted to convert a dataset from vb.net (2.0) to an .XLS file, by MS Jet. My national standard is using decimal commas, not decimal points for numbers signing the beginning of decimal places.
But the MS Jet Engine uses decimal point,in default. Therefore, in the Excel file only string formatted cells can welcome this data, not number formatted.
How can I solve or get around this problem? (with jet if it possible)
iviczl

View 4 Replies View Related

Converting Decimal To String W/O Decimal Point

Jul 23, 2005

I'd like to convert a Decimal value into a string so that the entireoriginal value and length remains intact but there is no decimal point.For example, the decimal value 6.250 is selected as 06250.Can this be done?

View 6 Replies View Related

Converting Decimal Point To Decimal Comma

Nov 30, 2007

Hi all,

I am designing some reports for a German branch of my company and need to replace decimal point with a comma and the thousand comma seperator with a decimal point.

e.g.
‚¬1,500,123.00 to ‚¬1.500.123,00

Is there a property that I can change in the report designer to allow this to happen or is this something I need to convert in a Stored Proc.

Any help would be much appreciated

Thanks!

View 5 Replies View Related

Decimal Limited To 4 Digits To Right Of Decimal?

Jun 18, 2007





I need to store decimal values: decimal(20,15) in my SQL Server 2005 database.

I load data from flat file, convert it using Data Conversion Task to decimal(with scale: 15) and try to save it using OLE DB Destination.



It works fine for 4 digits after the decimal (like 1.1234), but always failes for more than 4 digits (1.12345).

Is the decimal limited to scale 4 ???



Thank you for your help!

Anna





View 3 Replies View Related

Packed Decimal To Decimal Conversion

Jun 4, 2007

Hi,




I am having a file in which amount fields are given in a Packed Decimal format. Can anyone suggest me how I can read this data element from the file and convert it into SQL decimal datatype.

File is a fixed length. All the amount fields are given in Packed Decimal Format and rest of the fields are given in text format.
How can i identify and convert only those packed decimals using SQL/.Net.

Example : a row in a file that has some packed decimals
158203508540188236252EUR20BZK0030 Å“&
20060715 0001010100010101




Please help!



Thanks

Mirudhu

View 4 Replies View Related

Division In T-SQL

Feb 27, 2004

Hi,

Can anyone tell me why this doesn't return any decimals?


declare @f float
set @f = 6 / 18
print @f


All i get is 0... Is there something obvious i've done wrong?

TIA

View 3 Replies View Related

Division

Aug 9, 2000

Hi All

I am trying to devide 24 by 30 result it gives 0, but I wouldlike to get 0.8

how can I achieve this

I will apprecite your help, thank you

View 1 Replies View Related

Division By Zero Again

Mar 5, 2003

Hi all,

I have a prob with the following code...
in that the value of the amount of the year 2001
is 0 so it is showing me division by zero prob
can any one change the code and let me know the details


declare @year1 int
declare @year2 int
declare @month int

set @year1 = 2002
set @year2 = 2001
set @month =9

select
case when sum(case WHEN a.oper_year = @year1 THEN a.amount else 0 end) = sum(case WHEN a.oper_year = @year2 THEN a.amount else 0 end) then 0
else
(
(sum(case WHEN a.oper_year = @year1 THEN a.amount else 0 end) -
sum(case WHEN a.oper_year = @year2 THEN a.amount else 0 end))/
sum(case WHEN a.oper_year = @year2 THEN a.amount else 0 end)
)* 100 end as Percentage,
from oper_sundata a, oper_type_new b
where a.site_id = b.sun_site

Thanks

View 1 Replies View Related

Division Help

May 1, 2007

I'm very new to sql server and still don't know the in's and out's I have this:

Sum(Case WHEN dbo.THIT_RATIO_DETL.STATUS_CD = "B" or dbo.THIT_RATIO_DETL.STATUS_CD = "K" THEN 1 ELSE 0 END)/ Count(dbo.THIT_RATIO_DETL.SUBMISSION_NO)as Per_Quoted

I keep on getting 0, anybody have any ideas?

View 6 Replies View Related

Division By 0 In Query

Dec 17, 2007

In my query, there's a mathematical expression that takes a value from one table and divides it by another value (X).
The problem is that X can be 0 sometimes and then I get an error.
How can I prevent errors like this for the case of X=0?
In access I would use IIF function, but it doesn't appear in SQL SERVER views.
Thanks.

View 5 Replies View Related

Eliminate Division By Zero

Jul 31, 2007

This query is part of a larger query that updates a table that holds statistics for reporting. It yields actual Unit per Minute by plant by month. Some of the plants don't produce anything in certain months, so I'm ending up with a Divide by Zero error. I think I just need to stick another CASE statement in for each month, but that seems like it could get pretty ugly.

Any suggestions on how to improve this?


SELECT FL.REPORT_PLANT,
[JAN]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 1 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 1 THEN PC.HOURS*60 ELSE 0 END),
[FEB]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 2 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 2 THEN PC.HOURS*60 ELSE 0 END),
[MAR]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 3 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 3 THEN PC.HOURS*60 ELSE 0 END),
[APR]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 4 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 4 THEN PC.HOURS*60 ELSE 0 END),
[MAY]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 5 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 5 THEN PC.HOURS*60 ELSE 0 END),
[JUN]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 6 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 6 THEN PC.HOURS*60 ELSE 0 END),
[JUL]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 7 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 7 THEN PC.HOURS*60 ELSE 0 END),
[AUG]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 8 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 8 THEN PC.HOURS*60 ELSE 0 END),
[SEP]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 9 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 9 THEN PC.HOURS*60 ELSE 0 END),
[OCT]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 10 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 10 THEN PC.HOURS*60 ELSE 0 END),
[NOV]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 11 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 11 THEN PC.HOURS*60 ELSE 0 END),
[DEC]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 12 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 12 THEN PC.HOURS*60 ELSE 0 END)
FROM PRODUCTION_CMPLT PC INNER JOIN
FACILITY_LINES FL ON PC.MANUF_SITE = FL.MANUF_SITE AND
PC.PROD_LINE = FL.PROD_LINE INNER JOIN
PROD_MASTER PM ON PC.PRODUCT=PM.PRODUCT
WHERE YEAR(PC.MNTHYR) = YEAR(GETDATE()) AND PM.UOM<>'LB'
GROUP BY FL.REPORT_PLANT

View 14 Replies View Related

Division By Zero In Views

Mar 7, 2008

hi
in a view, i create a field like field1/field2.
in case that field2 is zero, i've got division by zero error.
how can i change the value to 0 when field2 is 0 for avoiding get error.

View 1 Replies View Related

Basic Division In SQL

Nov 20, 2007

I want to count the rows in two tables and then give a percentage as a result.

Something like:

SELECT Count(*) / (SELECT COUNT (*) FROM Table2) FROM Table1

Just not quite sure how to do this.


*Thanks*

View 14 Replies View Related

DIVISION ALWAYS RETURNS 0

Dec 11, 2007

It is common for me to need to create ratios from data in my database such as

SELECT
( list_value / sale_price ) as ratio
FROM
values


The value returned is always an integer whether decimal is cast or not. IE if sale_price is > list_value then 1 or 0 is returned instead of the percentage (ratio) as expected. Only whole numbers are returned.
BTW. Same is true in postgres db I have as well. What is it that I am doing wrong?

doco

View 5 Replies View Related

Help With Division In Query

Dec 14, 2007

Hi,

I have a table in which i have two colums say discription and counts.
the table has 10 rows.
This table is created by extracting data from other table means its not a table that exist in system.

Now in my last row i want discription as '%mailed' and count as row1/row2

can u tell me how to do that?

View 2 Replies View Related

Need Help With SQL Command / Division

Mar 15, 2008

SELECT [Record Number], Date, Car, Miles_Start, Miles_Stop, Gallons_FillUp, Miles_Stop - Miles_Start AS Miles_Total, Miles_Total / Gallons_FillUp AS MPG
FROM Sheet1_Table
ORDER BY Date DESC, Car

Hi,
I have Visual Studio 2008 Pro / Using VB 9.0 / SQL Server Express 2005. I am making a Miles per gallon calculator for my dad. All Fields /Cells are populated in two records excepting the Miles_Total & MPG. The Miles_Total Comes out fine but... the MPG does not . Any help with this Query would be greatly appreciated!
Jeff L

View 3 Replies View Related

Division By Zero Problem

Nov 15, 2007

I've got the following SQLSELECT count(*) FROM tablea AJOIN tableb B ON ..etc..WHERE a.string_val = 'test' ANDb.divider 0 ANDa.number 0 AND(a.number / b.divider 0)The b.divider value can be 0, but still I get the division by zeroerror message.I guess the reason is that the database performs the where statementsone at the time?So when performing the division a.number / b.divider it could get 0 inthe divider even if b.divider 0 is also part of the wherestatement??My question is then.. How can I work around this problem? Changing thedatabase to set b.divider always 0 is not an option

View 2 Replies View Related

UDT For Division Methods

Sep 4, 2007

Hi,

I plan to implement UDT for division methods.


The following fragment TSQL with zero check:


DECLARE @a int, @b int


SELECT



CASE WHEN @b = 0 THEN NULL

ELSE @a / @b
END



Clr UDT may look like this and script will be shorter.


DECLARE @a int, @b int


SELECT @a Type :: divide(@b)







Is that better to use UDT ?

Anyone have try this before?

View 8 Replies View Related

Division In SQL Results In 0

Jan 9, 2006

division in SQL results in 0
Posted on:  01/09/2006 09:20:13

  





declare @x int, @y int, @f float
set @x = 100
set @y = 2000
set @f = @x / @y
print @x
print @y
print @f


in the above code @f prints as 0 instead of 0.05
how would i correct that?

View 6 Replies View Related

Analysis :: How To Sum A Division Using MDX

Nov 9, 2015

I want to do something simple but I don't know how to do it using MDX. I searched a solution on the web but I found nothing. I want to SUM the result of the division of two measures. I tried using the WITH MEMBER Measures.sum_divided AS SUM(x/y) but the result in the cells are all #Error.

View 12 Replies View Related

Division Operation On Two Columns.

Mar 24, 2008

Hi to all,
Im using SQL Server 2000.
If i divide two integer column values, the result i am getting is integer. i.e
Update tblStudents SET Percentage=(AttendedClasses/TotalClasses);
AttendedClasses,TotalClasses are two column  names(Integer). I hav declared Percentage column as float.
The result im getting is integer value..
I want the actual float value(i.e Percentage)
How to Resolve this??
Help me with this issue..
thanks in advance.
 
 

View 1 Replies View Related

Format Results Of Division

May 1, 2008

I have a function that divides the results of 2 seperate datafiffs to provide a ratio. For some reason I cannot get the result to return as a decimal - it is only giving me integers. I have tried cast and converting both the idividual numbers and the results, no luck. If I write the query on it's own, casting the one of the values as decimal will return the right value. Any ideas what I am missing? Here is the function:



Create function fn_FTE(
@tkpr varchar(5),
@start datetime,
@end datetime)

Returns decimal
Begin
declare

@fte decimal


select @fte =

sum(datediff(dd,
(case
when eedatebeg < @start then @start
else eedatebeg
end),
(case
when eedateend > @end then @end
when eedateend is null then @end
else eedateend
end))) /datediff(dd,@start,@end)

from vw_employ_dates
where
eedatebeg < @end
and (eedateend > @start or eedateend is null)
and eudescrip = @tkpr


return @FTE
end


thanks for any help!

View 9 Replies View Related

SQL View - Division Problem

Oct 5, 2007

I have a view that I am works fine -

ALTER view [dbo].[_tec_SOP_POP_link] as
select SOP60100.SOPNUMBE as SOP_ORDER_NUMBER,
SOP60100.QTYRECVD as QtyReceivedSoFar,
SOP30200.sopnumbe as SOP_INV_NUMBER,
SOP60100.PONUMBER, SOP60100.QTYONPO as POQTY_REMAININGQTY,
POP10110.ITEMNMBR as ITEM_NUMBER,
POP10110.ITEMDESC as itemdescription, POP30310.UNITCOST as ReceivedCost,
Sum (case when pop30310.ponumber = sop60100.ponumber AND
pop30310.trxsorce like 'POIVC%' then pop30310.umqtyinb else 0 end) as TOTALINVOICEDMATCHED,
Sum (case when pop30310.ponumber = sop60100.ponumber AND
pop30310.trxsorce like 'POIVC%' then pop30310.extdcost else 0 end) as TOTALDOLLARSINVOICEDMATCHED,
from sop60100 inner join sop30200 on sop60100.sopnumbe = sop30200.ORIGNUMB AND sop30200.soptype = 3
inner join POP10110 on SOP60100.PONUMBER = POP10110.PONUMBER
inner join pop30310 on sop60100.ponumber = pop30310.ponumber
where SOP60100.ORD = POP10110.ORD
GROUP BY SOP60100.SOPNUMBE, SOP60100.QTYRECVD, SOP30200.sopnumbe, SOP60100.PONUMBER, SOP60100.QTYONPO,
POP10110.ITEMNMBR, POP10110.ITEMDESC, POP30310.UNITCOST, pop30310.umqtyinb


I want to add a column (UNITCOSTINVOICEDMATCHED)that divides TOTALDOLLARSINVOICEDMATCHED/TOTALINVOICEDMATCHED

I inserted the following (which did not work)-

Sum (TOTALDOLLARSINVOICEDMATCHED/TOTALINVOICEDMATCHED) as UNITCOSTINVOICEDMATCHED

error said - TOTALDOLLARSINVOICEDMATCHED column not recognized

The I inserted this statement - thinking I needed another case statement -

Sum(case when pop30310.ponumber = sop60100.ponumber AND
pop30310.trxsorce like 'POIVC%' then sum(pop30310.extdcost)/ sum(pop30310.umqtyinb)
else 0 end)as UNITCOSTINVOICEDMATCH

this returned the error -

Msg 130, Level 15, State 1, Procedure _tec_SOP_POP_link, Line 2
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Any thoughts?

View 3 Replies View Related

Simple Division Problem

Jul 23, 2005

I am trying to divide one number by another and I can't get it toreturn the right value....in general I want to see how many fruits I have, then get the number ofapples and divide the apples by that total to get the percentage ofapplesI haveDeclare @Val decimal(6,2)Set @Val=((select count(*) from Fruits where fruit='apples') /* = #APPLES/(select count(*) from Fruits) /* = TOTAL FRUITS)SELECT @Valin my actual case the apples=7 and the fruits=8but I can only return 87 or 875 (by multiplying by 100 or 1000) but Ican't get 87.5, which is the value I need.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved