Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Division By Zero


Hey all,
I want to use an ORDER BY statement that looks like:

Code:


ORDER BY TotalRating/NumberOfRatings



Obviously I am coming up with an error when NumberOfRatings is 0. Is there a way I can use an IF THEN statement to evaluate the ORDER BY to be 0 when NumberOfRatings is 0? I don't have any experience with IF THEN's in SQL, not sure how/where it is supposed to go.... Thanks in advance!


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Division In T-SQL
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 Replies !   View Related
Division By Zero Again
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 Replies !   View Related
Division
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 Replies !   View Related
Division Help
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 Replies !   View Related
Division By 0 In Query
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 Replies !   View Related
Division By Zero Problem
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 Replies !   View Related
Decimal Division
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 Replies !   View Related
Eliminate Division By Zero
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 Replies !   View Related
Division By Zero In Views
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 Replies !   View Related
Help With Division In Query
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 Replies !   View Related
DIVISION ALWAYS RETURNS 0
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 Replies !   View Related
Basic Division In SQL
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 Replies !   View Related
Need Help With SQL Command / Division
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 Replies !   View Related
UDT For Division Methods
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 Replies !   View Related
Decimal And Division
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 Replies !   View Related
Division In SQL Results In 0
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 Replies !   View Related
Division Operation On Two Columns.
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 Replies !   View Related
Simple Division Problem
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 Replies !   View Related
Simplifying Relational Division
If you think you can easily translate most relational divisionproblems to sql you should write a book.If your not even sure what relational division even is or how it cando done in a sane way then this should help:http://beyondsql.blogspot.com/2007/...relational.html

View Replies !   View Related
Format Results Of Division
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 Replies !   View Related
SQL Server Division With No Decimal
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 Replies !   View Related
SQL View - Division Problem
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 Replies !   View Related
Math - Division Question..
To set the stage, Tables are set up as such for this question:
-----------

Table: Answers
ID - Integer - Auto
Answer - Integer
QuestionID - Integer - FK from the QuizMaster table
StudentID - Integer - FK from the Students table

Table: QuizMaster

ID - Integer - Auto
Answer - Integer

Table: Students
ID - Integer - Auto
StudentName - Varchar(50)
----------
I would like to have an either a sql statement (1st) or a stored procedure (2nd) that would give me the percent correct for each student on the test.

In Access I could cheat and use this:



Code BlockSELECT Students.StudentName,
Count(Students.StudentName) AS TotalCorrect,
(SELECT Count(QuizMaster.ID) FROM QuizMaster;) AS TotalQuestions,
([TotalCorrect])/([TotalQuestions])*100 AS PercentValue
FROM (Answers INNER JOIN Students

ON Answers.StudentID = Students.ID)
INNER JOIN QuizMaster
ON (Answers.Answer = QuizMaster.Answer)
AND (Answers.QuestionID = QuizMaster.ID)
GROUP BY Students.StudentName
ORDER BY Students.StudentName, PercentValue;

Which is composed of the following...







Code Block

SELECT Students.StudentName,


Count(Students.StudentName) AS TotalCorrect
FROM (Answers INNER JOIN Students

ON Answers.StudentID = Students.ID)
INNER JOIN QuizMaster
ON (Answers.Answer = QuizMaster.Answer)
AND (Answers.QuestionID = QuizMaster.ID)


GROUP BY Students.StudentName;


SELECT Count(QuizMaster.ID) AS TotalQuestions
FROM QuizMaster;


([TotalCorrect])/([TotalQuestions])*100 AS PercentValue


But... that wont fly in SQL Server...What would work without using temp tables?
What would be the EASIEST way to do this?

Another way someone suggested is:




Code BlockCREATE PROCEDURE GetStudentGradeByID @StudentID Integer

AS

BEGIN


DECLARE @TotalQuestionsAsked as Integer;
DECLARE @StudentName as VarChar(50);
DECLARE @QuestionsCorrect as Integer;
DECLARE @PercentCorrect as Float;

set @TotalQuestionsAsked = select COUNT(*)FROM QuizMaster;
set @StudentName = select StudentName from Students where ID = @StudentID;

set @QuestionsCorrect = select count(Answers.ID) from Answers LEFT JOIN QuizMaster on Answers.QuestionID = QuizMaster.ID where StudentID = @StudentID AND QuizMaster.Answer = Answers.Answer;
set @PercentCorrect = (CAST (@QuestionsCorrect AS FLOAT) / CAST (@TotalQuestionsAsked AS FLOAT)) * 100.0;

select @QuestionsCorrect as TotalCorrect, @StudentName as Student, @TotalQuestionsAsked as NumQuestions, @PercentCorrect as CorrectPct;

END


But I need this as a datasource for a datagrid that would show all students etc... so using a parameter isn't really what I want.
So... could a stored procedure work for this?

View Replies !   View Related
Simple Division Problem
I am just learning SQL Server Express Edition and have been experimenting with various ANSI settings, etc. I think I may have accidentally set something incorrectly and don't know how to undo it. When I divide 3/2 I get 1.00 instead of 1.5.

I enter code:


DECLARE @special as decimal(3,2)

SET @special = 3/2

SELECT @special


The result comes back 1.00. Thanks for any help.

View Replies !   View Related
Division By Zero And Computed By Column
I've got two integer columns in the table, third one is computed by previous two ones division. That's fine, however sometimes can happen that divided by column is set to zero. How can i avoid division by zero exception, please? TIA

View Replies !   View Related
How Can I Show Decimals After Division Update
I have a table which I want to update by dividing one field intoanother. The update runs with no errors, but the results come out asonly a positive integer number.The datatype for the result field is float.The datatype for the other fields are int.I have tried testing by dividing 7 by 10000, which should give me0.0007. The result in the database is 0.How do I define a field to show the full value with decimal positions?If I enter .0007 into the field through the Enterprise Manager, it shows0007. When I update by dividing, it shows 0.Any help would be welcome.Joel*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View Replies !   View Related
Report Server: Division Between 2 Subtotal, Can This Be Done?
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 Replies !   View Related
Sub Subs, Counts, With Division Problem
Hello all, first post and really new to sub subs too! If any has time, I'm trying to retrieve data like this:
Reason Countee Perc
BENT HEAD 26 (% OF 26 TO 92)
OTHER 24 (% OF 24 TO 92)
etc...

I'm using the SQL below but can't seem to work out the percentage calculation. (The below only returns 0's). Thanks for any help up front!!!

SELECT Reason, COUNT(Reason) AS Countee, COUNT(Reason) /
(SELECT COUNT(ReasonCode)FROM rb_power.RBCOBBLETRK)AS Perc
FROM rb_power.RBCOBBLETRK
GROUP BY Reason
ORDER BY Countee DESC

Thanks!

View Replies !   View Related
Problem Calculating The Division Of 2 Columns
Hi
I have 2 tables

table1 which gives the schedule for day
id sat sun mon tue wed thu fri
1 40 50 35 75 80 90 95
2 30 40 45 45 95 85 45
3 40 50 25 25 75 75 55
4 50 60 35 55 65 65 65
5 70 35 75 65 30 35 70

table2 which gives total schedule for week
id week
1 500
2 600
3 550
4 450
5 650


i want to calculate to percentage schedule for each day for week for each value in id column

something like
sat/week where table1.id = table2.id

what im doing is
select (sat/week)
from table1
inner join table2
on table1.id = table2.id
group by id

its giving me error because im using group by clause

what can i do ??
please help

View Replies !   View Related
Division Errror In Excel Sheets


When I export a report which has one of its columns as quanity, whose value is -1/0 which denotes (dozens/pieces), I get an error #DIV/0!..Will making two seperate columns for the dozens and pieces help or is there any other way to avoid this error being displayed in the report?

thanks,
annej

View Replies !   View Related
Division By Zero Error, Trying To Execute The Adventureworks Project
Hello,

I m trying to create an analysis services project, I have the following system error : division by zero.
Then Ive tried to use the AdventureWorks analysis services project to see if I have also the same error , and yes I have the same error.

I live in Belgium, does the problem comes from the regional settings?
Ive tried to change them to English(United-States) and then I have this error : system error key not valid for use in specified state

Im only trying to execute the project, when I build everything goes fine.


Please help me

View Replies !   View Related
Division In A Precedence Constraint Expression Does Not Work Properly - Huh?
Greetings,

I have an expression in a precedence constraint that is returning false when it should return true. This is the expression that returns false:

((5500 / 9990) * 100) > 10

The following expression returns true. Idid the division (5500 / 9990) myself and substituted the resulting value:

((0.55055055055055055055055055055055) * 100) > 10

Why is the first expression returning false? I'm stuck in the mud up to my axles on this and I know I'll probably feel like a fool when I learn the answer...

Thanks,

BCB

View Replies !   View Related
Could Somone Clarify SQL Division And Explain The Examples, Thanks In Advance!
Code Snippet
SELECT 3308 / 15104





The above division will result with: 0





Code Snippet
SELECT 3308 / (15104 * 1)





The above division will result with: 0




Code Snippet
SELECT 3308 / (15104 * 1.0)





The above division will result with: 0.219014830


MY MAIN GOAL is to produce a result of: 0.2

I was thinking of using ROUND, then with some combination of RIGHT. Your explanation and advice is greatly appreciated! Thanks again!

View Replies !   View Related

Copyright 2005-08 www.BigResource.com, All rights reserved