Division question

I'm trying to divide 14/30. I would like to show my
output as 0.4666. Using the % gives me 14. How can I
get the output that I want?
TIA,
Vic
Because your SELECT statement doesn't specifiy any data types the values are
assumed to be integers. Integer division will return an integer result.
You can include a decimal which will give a decimal result:
SELECT 14/30.0
Or you can specify a data type explicitly:
SELECT 14/CAST(30 AS REAL)
Both of the above will return a result with decimal precision but if you
want a particular number of decimals in the output you will have to CAST or
ROUND the result or do the formatting in your client app.
David Portas
SQL Server MVP
|||http://www.aspfaq.com/2483
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Vic" <vduran@.specpro-inc.com> wrote in message
news:13c8101c4440d$fd1cc040$a101280a@.phx.gbl...
> I'm trying to divide 14/30. I would like to show my
> output as 0.4666. Using the % gives me 14. How can I
> get the output that I want?
> TIA,
> Vic

Division question

I'm trying to divide 14/30. I would like to show my
output as 0.4666. Using the % gives me 14. How can I
get the output that I want?
TIA,
VicBecause your SELECT statement doesn't specifiy any data types the values are
assumed to be integers. Integer division will return an integer result.
You can include a decimal which will give a decimal result:
SELECT 14/30.0
Or you can specify a data type explicitly:
SELECT 14/CAST(30 AS REAL)
Both of the above will return a result with decimal precision but if you
want a particular number of decimals in the output you will have to CAST or
ROUND the result or do the formatting in your client app.
David Portas
SQL Server MVP
--|||http://www.aspfaq.com/2483
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Vic" <vduran@.specpro-inc.com> wrote in message
news:13c8101c4440d$fd1cc040$a101280a@.phx
.gbl...
> I'm trying to divide 14/30. I would like to show my
> output as 0.4666. Using the % gives me 14. How can I
> get the output that I want?
> TIA,
> Vic

Division question

I'm trying to divide 14/30. I would like to show my
output as 0.4666. Using the % gives me 14. How can I
get the output that I want?
TIA,
VicBecause your SELECT statement doesn't specifiy any data types the values are
assumed to be integers. Integer division will return an integer result.
You can include a decimal which will give a decimal result:
SELECT 14/30.0
Or you can specify a data type explicitly:
SELECT 14/CAST(30 AS REAL)
Both of the above will return a result with decimal precision but if you
want a particular number of decimals in the output you will have to CAST or
ROUND the result or do the formatting in your client app.
--
David Portas
SQL Server MVP
--|||http://www.aspfaq.com/2483
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Vic" <vduran@.specpro-inc.com> wrote in message
news:13c8101c4440d$fd1cc040$a101280a@.phx.gbl...
> I'm trying to divide 14/30. I would like to show my
> output as 0.4666. Using the % gives me 14. How can I
> get the output that I want?
> TIA,
> Vic

Division operator

I'm trying to perform a simple mathematical operation on an integer. The integer is the result of the COUNT() function, I need to divide this by a number, say 5, and then round up that number.

For example, if the result of the operation was 52.4, it would become 53, if it were 52.6 it would be 53 and If it were 52, it would remain 52.

Is this kind of functionality built in to T-SQL ?

The main problem I'm having is that the result of COUNT(blah)/5 returns an integer, and ignores the remainder.

Thanks,
PaulIf you look up "division" in BOL you will get a better understanding of what is happenig.

you might try "cast(count(blah) as float)/5"

Division not returning whole numbers and not decimal values

I'm trying to divide 2 int columns and am expecting a result in a decimal
value, however, only whole values are being returned. First I tried
returning a calculated value from a select statement like this:
CREATE TABLE #tmp (col1 int, col2 int, TEST decimal(8,7))
INSERT INTO #tmp (col1, col2)
VALUES (7,5)
SELECT col2 / col1
FROM #tmp
But it returned zero. So then I tried to update the calculated value into a
decimal column but I got the same result.
UPDATE #tmp
SET TEST = col2 / col1
SELECT col1, col2, TEST FROM #tmp
Any help on this? THANKS!
moondaddy@.nospam.nospamYou've got to convert at least 1 of the values to a floating-point or decima
l
type first. e.g:
select 4 / 3 -- returns int
select convert(float, 4) / 3 -- returns float
"moondaddy" wrote:

> I'm trying to divide 2 int columns and am expecting a result in a decimal
> value, however, only whole values are being returned. First I tried
> returning a calculated value from a select statement like this:
>
> CREATE TABLE #tmp (col1 int, col2 int, TEST decimal(8,7))
> INSERT INTO #tmp (col1, col2)
> VALUES (7,5)
> SELECT col2 / col1
> FROM #tmp
> But it returned zero. So then I tried to update the calculated value into
a
> decimal column but I got the same result.
> UPDATE #tmp
> SET TEST = col2 / col1
> SELECT col1, col2, TEST FROM #tmp
> Any help on this? THANKS!
> --
> moondaddy@.nospam.nospam
>
>

Dividing two Count() Results

Just a quick question, how do I divide the results of these two queries
together ?

(select count (*) as ontime from schedule where actualarrivaldate <=
estimatearrivaldate)

(select count (*) as total from schedule)

Thanks for any help
RobertSELECT ontime, total, ontime/CAST(total AS REAL)
FROM
(SELECT COUNT(CASE WHEN actualarrivaldate <= estimatearrivaldate
THEN 1 END), COUNT(*)
FROM schedule) AS X(ontime,total)

(untested)

--
David Portas
----
Please reply only to the newsgroup
--|||Thanks for the quick reply, the query worked perfect.
Cheers
Robert

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:nMadnd4h2ZNOrveiRVn-hw@.giganews.com...
> SELECT ontime, total, ontime/CAST(total AS REAL)
> FROM
> (SELECT COUNT(CASE WHEN actualarrivaldate <= estimatearrivaldate
> THEN 1 END), COUNT(*)
> FROM schedule) AS X(ontime,total)
> (untested)
> --
> David Portas
> ----
> Please reply only to the newsgroup
> --

Dividing subtotal in a matrix

I want to divide the subtotal of a matrix. Can I do ?

Hiya,

Try the following....= ReportItems!<Field_Name>.Value/<Divisor>

where <Field_Name> is the name of the subtotal cell in your matrix

dividing a large flat file into small files

Hi ,

Is there any method by which I can divide the large flat file into certain number of small files keeping the header in each of the sub files?

Regards,

Prash

See this post by John:
http://agilebi.com/cs/blogs/jwelch/archive/2007/06/03/multi-file-output-destination-script-component.aspx|||

prashant550806 wrote:

Hi ,

Is there any method by which I can divide the large flat file into certain number of small files keeping the header in each of the sub files?

Regards,

Prash

This should help as well:

Splitting a file into multiple files

(http://blogs.conchango.com/jamiethomson/archive/2005/12/04/SSIS-Nugget_3A00_-Splitting-a-file-into-multiple-files.aspx)

-Jamie

Divide table into two parts

Hi all!

I wanna divide a table into two parts by inserting a page break after a certain row (I need only one page break). How can I achieve this goal?

Thanks.

What do you mean by page break, do you mean that the first part stays in the table and the other part goes to another table ? Which is the ORDER criteria that you want these tables to order for ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||I've got several controls just after table and i need that controls are displaied at the same page as table (at least a row must be at the same page with controls). At the moment I can get a situation when table is displaied in one page and controls are dispaied on another page.

Divide Sum of 2 Different Groups

Hi,
I hope I'm making this harder than it is but I am trying to divide the sum
of two different groups to obtain a percentage.
__________________________________________
Group #1 "grpLevel2" =Sum(Fields!amt_3.Value)
__________________________________________
Group #2 "grpResID" =Sum(Fields!amt_3.Value)
I need to divide the sum of amt_3 for group ResIDAmt by the sum of amt_3 for
group grpLevel2 and multiply it by 100.
I have tried every way I can think of to do this with varying errors.....
Any help would be greatly appreciated.Bill, if you are trying to find the proportion of the total, then it is
harder than you think, but not insurmountable.
You have probably tried something like;
=Sum(Fields!amt_3.Value, "grpResID") / Sum(Fields!amt_3.Value,
"grpLevel2") * 100
I believe the reason this doesn't work is that, unlike Crystal, RS
doesn't have an extra pass after all totals have been calculated in
order to keep the performance up.
So the answer is to have the group 1 totals on the individual data
lines. The query would look something like this;
Select
Fld1,
Fld2,
amt_3,
Total = (Select Sum(amt_3) From MyTable B
Where A.Fld1 = B.Fld1)
From MyTable A
Then in the field you can do;
=Sum(Fields!amt_3.Value) / First(Fields!Total.Value) * 100
Note the use of FIRST, also if you use percent format code 'p' in the
cell, you don't need to multiply by 100, it will do it for you.
You will also need to code for 'divide by zero'.
Regards
Chris
BillD wrote:
> Hi,
> I hope I'm making this harder than it is but I am trying to divide
> the sum of two different groups to obtain a percentage.
> __________________________________________
> Group #1 "grpLevel2" =Sum(Fields!amt_3.Value)
> __________________________________________
> Group #2 "grpResID" =Sum(Fields!amt_3.Value)
> I need to divide the sum of amt_3 for group ResIDAmt by the sum of
> amt_3 for group grpLevel2 and multiply it by 100.
> I have tried every way I can think of to do this with varying
> errors.....
> Any help would be greatly appreciated.|||Chris,
Thank You for taking the time to reply. Finally got it to work without
drinking.
"Chris McGuigan" wrote:
> Bill, if you are trying to find the proportion of the total, then it is
> harder than you think, but not insurmountable.
>
> You have probably tried something like;
> =Sum(Fields!amt_3.Value, "grpResID") / Sum(Fields!amt_3.Value,
> "grpLevel2") * 100
> I believe the reason this doesn't work is that, unlike Crystal, RS
> doesn't have an extra pass after all totals have been calculated in
> order to keep the performance up.
> So the answer is to have the group 1 totals on the individual data
> lines. The query would look something like this;
> Select
> Fld1,
> Fld2,
> amt_3,
> Total = (Select Sum(amt_3) From MyTable B
> Where A.Fld1 = B.Fld1)
> From MyTable A
> Then in the field you can do;
> =Sum(Fields!amt_3.Value) / First(Fields!Total.Value) * 100
> Note the use of FIRST, also if you use percent format code 'p' in the
> cell, you don't need to multiply by 100, it will do it for you.
> You will also need to code for 'divide by zero'.
> Regards
> Chris
>
> BillD wrote:
> > Hi,
> >
> > I hope I'm making this harder than it is but I am trying to divide
> > the sum of two different groups to obtain a percentage.
> > __________________________________________
> > Group #1 "grpLevel2" =Sum(Fields!amt_3.Value)
> > __________________________________________
> > Group #2 "grpResID" =Sum(Fields!amt_3.Value)
> >
> > I need to divide the sum of amt_3 for group ResIDAmt by the sum of
> > amt_3 for group grpLevel2 and multiply it by 100.
> >
> > I have tried every way I can think of to do this with varying
> > errors.....
> >
> > Any help would be greatly appreciated.
>|||I dunno about you, but my more creative solutions often come after a
pint or two!
Chris
BillD wrote:
> Chris,
> Thank You for taking the time to reply. Finally got it to work
> without drinking.
>
> "Chris McGuigan" wrote:
> > Bill, if you are trying to find the proportion of the total, then
> > it is harder than you think, but not insurmountable.
> >
> >
> > You have probably tried something like;
> > =Sum(Fields!amt_3.Value, "grpResID") / Sum(Fields!amt_3.Value,
> > "grpLevel2") * 100
> >
> > I believe the reason this doesn't work is that, unlike Crystal, RS
> > doesn't have an extra pass after all totals have been calculated in
> > order to keep the performance up.
> >
> > So the answer is to have the group 1 totals on the individual data
> > lines. The query would look something like this;
> >
> > Select
> > Fld1,
> > Fld2,
> > amt_3,
> > Total = (Select Sum(amt_3) From MyTable B
> > Where A.Fld1 = B.Fld1)
> > From MyTable A
> >
> > Then in the field you can do;
> > =Sum(Fields!amt_3.Value) / First(Fields!Total.Value) * 100
> >
> > Note the use of FIRST, also if you use percent format code 'p' in
> > the cell, you don't need to multiply by 100, it will do it for you.
> >
> > You will also need to code for 'divide by zero'.
> >
> > Regards
> > Chris
> >
> >
> > BillD wrote:
> >
> > > Hi,
> > >
> > > I hope I'm making this harder than it is but I am trying to divide
> > > the sum of two different groups to obtain a percentage.
> > > __________________________________________
> > > Group #1 "grpLevel2" =Sum(Fields!amt_3.Value)
> > > __________________________________________
> > > Group #2 "grpResID" =Sum(Fields!amt_3.Value)
> > >
> > > I need to divide the sum of amt_3 for group ResIDAmt by the sum of
> > > amt_3 for group grpLevel2 and multiply it by 100.
> > >
> > > I have tried every way I can think of to do this with varying
> > > errors.....
> > >
> > > Any help would be greatly appreciated.
> >
> >

Divide Integers

I'm trying to divide 2 integers to return a column of C's per thousand with
(Failures / Dropped) * 1000.
I've tried different version of float, cast as decimal etc. I'd like 4-5
didgits after the decimal. What is the correct way to divide integers to
return a usable number.
Yr Mnth Dropped Failures
-- -- -- --
2005 July 126493 610
2005 August 207325 955
2005 September 89714 742
2005 October 225112 1142
2005 November 186264 791
2005 December 146901 774
2006 January 103096 510Try
SELECT CAST((CAST(Failures AS numeric) / Dropped) * 1000 AS decimal(8,4))
FROM [YourTable]
"Paul Ilacqua" wrote:

> I'm trying to divide 2 integers to return a column of C's per thousand wi
th
> (Failures / Dropped) * 1000.
> I've tried different version of float, cast as decimal etc. I'd like 4-5
> didgits after the decimal. What is the correct way to divide integers to
> return a usable number.
> Yr Mnth Dropped Failures
> -- -- -- --
> 2005 July 126493 610
> 2005 August 207325 955
> 2005 September 89714 742
> 2005 October 225112 1142
> 2005 November 186264 791
> 2005 December 146901 774
> 2006 January 103096 510
>
>|||Paul Ilacqua (pilacqu2@.twcny.rr.com) writes:
> I'm trying to divide 2 integers to return a column of C's per thousand
> with (Failures / Dropped) * 1000. I've tried different version of
> float, cast as decimal etc. I'd like 4-5 didgits after the decimal. What
> is the correct way to divide integers to return a usable number.
Does one of:
convert(decimal(18,4), 1E0 * Failures / Dropped * 1000)
round(1E0 * Failures / Dropped * 1000, 4)
meet your requirements? Note that the last expression is float, so it
may have 000000001 or similar at the end when you look at it in Query
Analyzer.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||That does work fine.... Thank You very much.
It is sure odd numeric behavior... calculate it in Access or Excel and
it's automatic. Would you call this method "standard" in handling integer
division?
Thanks again
Paul
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:FF7A96CF-B908-4FCD-BE98-6F6AB8FFDC8A@.microsoft.com...
> Try
> SELECT CAST((CAST(Failures AS numeric) / Dropped) * 1000 AS decimal(8,4))
> FROM [YourTable]
>
> --
> "Paul Ilacqua" wrote:
>|||Did you bother to look up the basic rules of integer math in SQL?|||Paul Ilacqua (pilacqu2@.twcny.rr.com) writes:
> That does work fine.... Thank You very much.
> It is sure odd numeric behavior... calculate it in Access or Excel and
> it's automatic. Would you call this method "standard" in handling integer
> division?
Exactly what is odd? integer/integer meaning integer division appears
in several other programming languages as well. And the fact that you
need to round or convert the result to get the number of desired decimals
is not strange at all - how should SQL Server be able to guess what you
want?
Yes, Excel has a number of rules for precisely that, guessing. Sometimes
it works, sometimes it does not. I have some CSV files around which
has data like 19991112093212, which Excel presents as float values
each time I open the file. In fact this is a string (date and time).
But Excel is an interactive tool, so mis-guesses can easily be corrected.
A server like SQL Server must work after more stringent rules.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,
I agree... after I replied I realized that's why I like SQL Server over
Access or Excel (among other reasons) is that allows a finer control over
things like number formatting. Excel uses it's best guess a lot where it is
not always the desired result. "Odd" is a word that is replaced by
"understanding" after you learn something and that's why I frequent these
boards. (and sometimes take some hits for it)
Thanks again
Paul
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9755642E25FB4Yazorman@.127.0.0.1...
> Paul Ilacqua (pilacqu2@.twcny.rr.com) writes:
> Exactly what is odd? integer/integer meaning integer division appears
> in several other programming languages as well. And the fact that you
> need to round or convert the result to get the number of desired decimals
> is not strange at all - how should SQL Server be able to guess what you
> want?
> Yes, Excel has a number of rules for precisely that, guessing. Sometimes
> it works, sometimes it does not. I have some CSV files around which
> has data like 19991112093212, which Excel presents as float values
> each time I open the file. In fact this is a string (date and time).
> But Excel is an interactive tool, so mis-guesses can easily be corrected.
> A server like SQL Server must work after more stringent rules.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

divide INT get decimal output

I have written the following query and even though I used CAST if the
number is less then 1 it show up as 0. Here is the query:

select cast(count(SafetyTrainingYN) AS FLOAT)
from ICPCDATA
where SafetyTrainingYN = 1 /
(select count(SafetyTrainingYN)
from ICPCDATA)

Any help would be much appreciated.

Thanks,

jp

remove _nospam_ for my email

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Josh, is this what you're after?

SELECT
(SELECT CAST(COUNT(*) AS FLOAT)
FROM ICPCDATA
WHERE SafetyTrainingYN=1) /
(SELECT COUNT(SafetyTrainingYN)
FROM ICPCDATA)
AS PercentYes

In your query, you're looking for the count of rows where SafetyTrainingYN
is equal to the fraction 1/count(...) (zero when the table has more than 1
row due to integer division).

Hope that helps,
Rich

"Josh Phillips" <phillips3_nospam_@.hotmail.com> wrote in message
news:40914d9e$0$203$75868355@.news.frii.net...
> I have written the following query and even though I used CAST if the
> number is less then 1 it show up as 0. Here is the query:
> select cast(count(SafetyTrainingYN) AS FLOAT)
> from ICPCDATA
> where SafetyTrainingYN = 1 /
> (select count(SafetyTrainingYN)
> from ICPCDATA)
> Any help would be much appreciated.
> Thanks,
> jp
>
> remove _nospam_ for my email
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Divide by zero with Sub Query

Hello,
I need to dived a number by a summed value from a subquery. Any
suggestions on how to trap for zeros would be appreciated.
Thanks in advance,
sck10
SELECT
tsp01.StoreRevenue /
SELECT SUM(tft02.TotalFunded)
FROM
MyTable Tsp02
WHERE (Tsp02.Track_ID = Tsp01.Track_ID)
GROUP BY Tsp02.Track_ID)
What do you want to do if you have found a 0?
Try
select 20 / nullif(2, 0) -- returns 10
select 20 / nullif(0, 0) -- returns NULL
Ben Nevarez, MCDBA, OCP
Database Administrator
"sck10" wrote:

> Hello,
> I need to dived a number by a summed value from a subquery. Any
> suggestions on how to trap for zeros would be appreciated.
> --
> Thanks in advance,
> sck10
> SELECT
> tsp01.StoreRevenue /
> SELECT SUM(tft02.TotalFunded)
> FROM
> MyTable Tsp02
> WHERE (Tsp02.Track_ID = Tsp01.Track_ID)
> GROUP BY Tsp02.Track_ID)
>
>
|||SELECT
CASE
WHEN SUM(tft02.TotalFunded)=0 THEN 0 ELSE
SUM(tsp01.StoreRevenue)/SUM(tft02.TotalFunded)
END
FROM tsp01, tsp02
where (tsp02.Track_ID = tsp01.Track_ID)
GROUP BY Tsp02.Track_ID
let me know if this works for you.
|||Thanks Zomer,
This worked...
"zomer" <noneee@.gmail.com> wrote in message
news:1143485957.082088.145680@.i39g2000cwa.googlegr oups.com...
> SELECT
> CASE
> WHEN SUM(tft02.TotalFunded)=0 THEN 0 ELSE
> SUM(tsp01.StoreRevenue)/SUM(tft02.TotalFunded)
> END
> FROM tsp01, tsp02
> where (tsp02.Track_ID = tsp01.Track_ID)
> GROUP BY Tsp02.Track_ID
> let me know if this works for you.
>

Divide by zero with Sub Query

Hello,
I need to dived a number by a summed value from a subquery. Any
suggestions on how to trap for zeros would be appreciated.
--
Thanks in advance,
sck10
SELECT
tsp01.StoreRevenue /
SELECT SUM(tft02.TotalFunded)
FROM
MyTable Tsp02
WHERE (Tsp02.Track_ID = Tsp01.Track_ID)
GROUP BY Tsp02.Track_ID)What do you want to do if you have found a 0?
Try
select 20 / nullif(2, 0) -- returns 10
select 20 / nullif(0, 0) -- returns NULL
Ben Nevarez, MCDBA, OCP
Database Administrator
"sck10" wrote:
> Hello,
> I need to dived a number by a summed value from a subquery. Any
> suggestions on how to trap for zeros would be appreciated.
> --
> Thanks in advance,
> sck10
> SELECT
> tsp01.StoreRevenue /
> SELECT SUM(tft02.TotalFunded)
> FROM
> MyTable Tsp02
> WHERE (Tsp02.Track_ID = Tsp01.Track_ID)
> GROUP BY Tsp02.Track_ID)
>
>|||SELECT
CASE
WHEN SUM(tft02.TotalFunded)=0 THEN 0 ELSE
SUM(tsp01.StoreRevenue)/SUM(tft02.TotalFunded)
END
FROM tsp01, tsp02
where (tsp02.Track_ID = tsp01.Track_ID)
GROUP BY Tsp02.Track_ID
let me know if this works for you.|||Thanks Zomer,
This worked...
"zomer" <noneee@.gmail.com> wrote in message
news:1143485957.082088.145680@.i39g2000cwa.googlegroups.com...
> SELECT
> CASE
> WHEN SUM(tft02.TotalFunded)=0 THEN 0 ELSE
> SUM(tsp01.StoreRevenue)/SUM(tft02.TotalFunded)
> END
> FROM tsp01, tsp02
> where (tsp02.Track_ID = tsp01.Track_ID)
> GROUP BY Tsp02.Track_ID
> let me know if this works for you.
>

Divide by zero with Sub Query

Hello,
I need to dived a number by a summed value from a subquery. Any
suggestions on how to trap for zeros would be appreciated.
--
Thanks in advance,
sck10
SELECT
tsp01.StoreRevenue /
SELECT SUM(tft02.TotalFunded)
FROM
MyTable Tsp02
WHERE (Tsp02.Track_ID = Tsp01.Track_ID)
GROUP BY Tsp02.Track_ID)What do you want to do if you have found a 0?
Try
select 20 / nullif(2, 0) -- returns 10
select 20 / nullif(0, 0) -- returns NULL
Ben Nevarez, MCDBA, OCP
Database Administrator
"sck10" wrote:

> Hello,
> I need to dived a number by a summed value from a subquery. Any
> suggestions on how to trap for zeros would be appreciated.
> --
> Thanks in advance,
> sck10
> SELECT
> tsp01.StoreRevenue /
> SELECT SUM(tft02.TotalFunded)
> FROM
> MyTable Tsp02
> WHERE (Tsp02.Track_ID = Tsp01.Track_ID)
> GROUP BY Tsp02.Track_ID)
>
>|||SELECT
CASE
WHEN SUM(tft02.TotalFunded)=0 THEN 0 ELSE
SUM(tsp01.StoreRevenue)/SUM(tft02.TotalFunded)
END
FROM tsp01, tsp02
where (tsp02.Track_ID = tsp01.Track_ID)
GROUP BY Tsp02.Track_ID
let me know if this works for you.|||Thanks Zomer,
This worked...
"zomer" <noneee@.gmail.com> wrote in message
news:1143485957.082088.145680@.i39g2000cwa.googlegroups.com...
> SELECT
> CASE
> WHEN SUM(tft02.TotalFunded)=0 THEN 0 ELSE
> SUM(tsp01.StoreRevenue)/SUM(tft02.TotalFunded)
> END
> FROM tsp01, tsp02
> where (tsp02.Track_ID = tsp01.Track_ID)
> GROUP BY Tsp02.Track_ID
> let me know if this works for you.
>

Divide By Zero in the Where Condition

Hi All,
In my SQL I have Where Condition is as follows
Select * From Table1...
WHERE StatusDate BETWEEN '01-jan-2005' and '01-Mar-2005'
And (100 - (Price/MarketValue *100)) > abs(10))
But some of my MarketValue in the table having zero values, I am getting
following error message.
"Divide by zero error encountered."
How to handle this situation. Please help me...
Thanks,
Kannan
kannanBelow are two options you can use to work around this:
CREATE TABLE Table1
(
StatusDate SMALLDATETIME,
Price NUMERIC(7, 2),
MarketValue NUMERIC(7, 2)
)
INSERT Table1 SELECT '1 Feb 2005', 30.00, 25.00
INSERT Table1 SELECT '2 Feb 2005', 50.00, 0.00
INSERT Table1 SELECT '3 Feb 2005', 10.00, 15.00
INSERT Table1 SELECT '4 Feb 2005', 20.00, 300
You can ignore the arithmetic error and turn the error message off by
setting the following setting the follow options
ie.
SET ARITHABORT OFF
SET ARITHIGNORE ON
SET ANSI_WARNINGS OFF
GO
SELECT *
FROM Table1
WHERE StatusDate BETWEEN '01-jan-2005' and '01-Mar-2005'
AND 100 - (Price/MarketValue * 100) > ABS(10)
Or you can exclude all results where the Market Value is 0
ie.
SELECT *
FROM Table1
WHERE StatusDate BETWEEN '01-jan-2005' and '01-Mar-2005'
AND MarketValue <> 0
AND 100 - (Price/MarketValue * 100) > ABS(10)
- Peter Ward
WARDY IT Solutions
"kannan" wrote:

> Hi All,
> In my SQL I have Where Condition is as follows
> Select * From Table1...
> WHERE StatusDate BETWEEN '01-jan-2005' and '01-Mar-2005'
> And (100 - (Price/MarketValue *100)) > abs(10))
> But some of my MarketValue in the table having zero values, I am getting
> following error message.
> "Divide by zero error encountered."
> How to handle this situation. Please help me...
> Thanks,
> Kannan
>
> --
> kannan|||Try using function "nullif".
...
And (100 - (Price / nullif(MarketValue, 0) *100)) > abs(10))
AMB
"kannan" wrote:

> Hi All,
> In my SQL I have Where Condition is as follows
> Select * From Table1...
> WHERE StatusDate BETWEEN '01-jan-2005' and '01-Mar-2005'
> And (100 - (Price/MarketValue *100)) > abs(10))
> But some of my MarketValue in the table having zero values, I am getting
> following error message.
> "Divide by zero error encountered."
> How to handle this situation. Please help me...
> Thanks,
> Kannan
>
> --
> kannan|||Since the SQL Server query optimizer is free to evaluate
things in whatever order is most efficient, adding AND MarketValue <> 0
is not guaranteed to eliminate division by zero.
Alejandro's solution with NULLIF will work, as will the
equivalent use of a CASE expression:
...
Price/CASE WHEN MarketValue = 0 THEN 1 ELSE MarketValue END
...
Depending on what result is needed for rows where MarketValue
equals zero, NULL or another constant value may be a more
appropriate choice than 1 as the alternative of the CASE expression.
Steve Kass
Drew University
P. Ward wrote:
>Below are two options you can use to work around this:
>CREATE TABLE Table1
> (
> StatusDate SMALLDATETIME,
> Price NUMERIC(7, 2),
> MarketValue NUMERIC(7, 2)
> )
>INSERT Table1 SELECT '1 Feb 2005', 30.00, 25.00
>INSERT Table1 SELECT '2 Feb 2005', 50.00, 0.00
>INSERT Table1 SELECT '3 Feb 2005', 10.00, 15.00
>INSERT Table1 SELECT '4 Feb 2005', 20.00, 300
>
>You can ignore the arithmetic error and turn the error message off by
>setting the following setting the follow options
>ie.
>SET ARITHABORT OFF
>SET ARITHIGNORE ON
>SET ANSI_WARNINGS OFF
>GO
>SELECT *
>FROM Table1
>WHERE StatusDate BETWEEN '01-jan-2005' and '01-Mar-2005'
>AND 100 - (Price/MarketValue * 100) > ABS(10)
>
>Or you can exclude all results where the Market Value is 0
>ie.
>SELECT *
>FROM Table1
>WHERE StatusDate BETWEEN '01-jan-2005' and '01-Mar-2005'
>AND MarketValue <> 0
>AND 100 - (Price/MarketValue * 100) > ABS(10)
>
>- Peter Ward
>WARDY IT Solutions
>
>"kannan" wrote:
>
>|||Do you need the records with 0 market value in the resultset? I am assuming
you don't since it wont return true to your formula if it's 0, simply exclud
e
them by adding 'and Market > 0'.
Regards,
Willson
"kannan" wrote:

> Hi All,
> In my SQL I have Where Condition is as follows
> Select * From Table1...
> WHERE StatusDate BETWEEN '01-jan-2005' and '01-Mar-2005'
> And (100 - (Price/MarketValue *100)) > abs(10))
> But some of my MarketValue in the table having zero values, I am getting
> following error message.
> "Divide by zero error encountered."
> How to handle this situation. Please help me...
> Thanks,
> Kannan
>
> --
> kannan

divide by zero exception

How I can rectify divide by zero exception in stored procedure

By checking first, to make sure you're not about to divide by zero ? I suspect a CASE statement is the easiest way to do this.

|||i know it, but actually I want that display 0 or my error message instead of server error message.|||

OK - so what do you need to know ? In your stored proc, return the result of the divide if it's not going to divide by zero, or zero if it is. Then you can check for 0 and show an error if you prefer.

|||

when i write Query like

select 4/0 then it display error msg - 8134

but i don't wan't to display it, i want it as 0.

i have too much table like apr0405i, aprs0506i similary as years wise table. So i create sp and passing '0405' value of parameter of sp which select the table of corresponding year then it display value. but in 0506 then it display the divide by zero exception. so i want to suppress this error message and display 0. because it can't possible to use case with big sql query since i am using more than 15 sql query in one query statement and stored that value in parameter & then print value of paramter

|||

OK - so your database design is not so great ? You create a table for each month ?

I don't know of any way to make SQL Server return 0 when you divide by 0.

|||Joining this thread a little late, oh well....

As far as I am aware, you can't make SQL Server return 0 but you can override the default behaviour (suppressing error messages and the like) and define your own behaviour, such as returning 0. For example:

/*
Override default behaviour
*/
SET ARITHABORT OFF -- Divide by 0 does not halt execution
SET ARITHIGNORE ON -- Divide by 0 does not display error message
SET ANSI_WARNINGS OFF -- Divide by 0 does not display error message

DECLARE @.returnOfCalc As INT

SELECT @.returnOfCalc = 4/0
IF @.returnOfCalc IS NULL
BEGIN
SET @.returnOfCalc = 0
PRINT @.returnOfCalc -- Optional, just as a demo of what can be done
END

/*
Restore default behaviour
*/
SET ARITHABORT ON
SET ARITHIGNORE OFF
SET ANSI_WARNINGS ON

So in your stored procedure, you would issue the first bunch of SET statements at the beginning of the procedure (to override default behaviour) and just before the end of the stored procedure you would issue the second set of SET statements (to restore the default behaviour).

I really don't suggest you mess with the default behaviour in SQL Server, but if you really want/need to then knock yourself out. Just remember to be a good citizen and reset the default behaviour when you finish.

Hope that helps a bit, but sorry if it doesn't
|||

You can do something like:

select coalesce(@.i/nullif(@.j, 0), 0)

-- or

select case @.j when 0 then @.j else @.i/@.j end

|||

use the try... catch

feature of sql2k5

|||

Thanks for ur suggestion, but I already solved it by

case method it goes too much bigger but it gives satisfied results

well, ur answer giving very well theoritical concept, Thank u once again, Next I will send u another query, if i have

|||Thank U chandra ji once again, i was already using same ideas which u send. But Its very well felling inside me, Thanks once again

Divide by zero error?

I have several situations where I encounter the following error:
[Microsoft][ODBC SQL SERVER DRIVER][SQL Server]Divide by zero error
encountered.
I'm sure this topic has been covered hundreds of times - but how would I
stop this error in the following query:
SELECT [Pharmacy Exp] / TotalMem AS PhExpPMPM
FROM Sheet1$
WHERE ([Pharmacy Exp] <> '') AND (Period = '3Q04') AND (Domicile LIKE
'%ct%' OR
Domicile LIKE '%ma%' OR
Domicile LIKE '%nh%' OR
Domicile LIKE '%me%' OR
Domicile LIKE '%ri%' OR
Domicile LIKE '%vt%' OR
Domicile LIKE '%ny%' OR
Domicile LIKE '%nj%')
This issues comes about because either [pharmacy exp] or TotalMem is either
null or zero. I don't want to exclude the data from my query (ie: using
where [pharmacy exp] <>'') but would rather show a zero for the data point.SELECT CASE (TotalMem WHEN 0 THEN 0
ELSE [Pharmacy Exp] / TotalMem END PhExpPMPM
FROM Sheet1$
WHERE ([Pharmacy Exp] <> '') AND (Period = '3Q04') AND (Domicile LIKE
'%ct%' OR
Domicile LIKE '%ma%' OR
Domicile LIKE '%nh%' OR
Domicile LIKE '%me%' OR
Domicile LIKE '%ri%' OR
Domicile LIKE '%vt%' OR
Domicile LIKE '%ny%' OR
Domicile LIKE '%nj%')
Jacco Schalkwijk
SQL Server MVP
"William" <da@.northernit.net> wrote in message
news:KlU3e.6431$6a5.1685@.twister.nyroc.rr.com...
>I have several situations where I encounter the following error:
> [Microsoft][ODBC SQL SERVER DRIVER][SQL Server]Divide by zero error
> encountered.
> I'm sure this topic has been covered hundreds of times - but how would I
> stop this error in the following query:
>
> SELECT [Pharmacy Exp] / TotalMem AS PhExpPMPM
> FROM Sheet1$
> WHERE ([Pharmacy Exp] <> '') AND (Period = '3Q04') AND (Domicile LIKE
> '%ct%' OR
> Domicile LIKE '%ma%' OR
> Domicile LIKE '%nh%' OR
> Domicile LIKE '%me%' OR
> Domicile LIKE '%ri%' OR
> Domicile LIKE '%vt%' OR
> Domicile LIKE '%ny%' OR
> Domicile LIKE '%nj%')
> This issues comes about because either [pharmacy exp] or TotalMem is
> either
> null or zero. I don't want to exclude the data from my query (ie: using
> where [pharmacy exp] <>'') but would rather show a zero for the data
> point.
>

Divide by zero error! Help!

I don't understand why I get the error "Server: Msg 8134, Level 16,
State 1, Line 1
Divide by zero error encountered."
I check for 0, actually if I change the statement after ELSE to 2, it
will run with no issue and get 1 since the when statement is 0 in this
case. Please help.
SELECT
CASE WHEN SUM(CONVERT(INT, ICFPM_USER_12)* CONVERT(INT,
ICFPM_USER_14))= 0 THEN 1
ELSE SUM(REV_ORDER_QTY/(CONVERT(INT, ICFPM_USER_12)*CONVERT(INT,
ICFPM_USER_14)))
END
FROM SOFOD, ICFPM
WHERE SOFOD.PART_ID = ICFPM.PART_ID AND SOFOD.SO_ID = '105706'Hi
Don't assume that the execution order of an ELSE will follow the order you
coded. It may evaluate both CASEs in parallel and then use the output later.
During query execution, your query may be run differently to what you think.
This can be influenced by number of processors, RAM available, indexes and
statistics.
You need to make sure that your query does not have the possibility of
failing, no matter the code execution path.
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
<zod91@.yahoo.com> wrote in message
news:1149560228.320495.202850@.j55g2000cwa.googlegroups.com...
>I don't understand why I get the error "Server: Msg 8134, Level 16,
> State 1, Line 1
> Divide by zero error encountered."
> I check for 0, actually if I change the statement after ELSE to 2, it
> will run with no issue and get 1 since the when statement is 0 in this
> case. Please help.
>
> SELECT
> CASE WHEN SUM(CONVERT(INT, ICFPM_USER_12)* CONVERT(INT,
> ICFPM_USER_14))= 0 THEN 1
> ELSE SUM(REV_ORDER_QTY/(CONVERT(INT, ICFPM_USER_12)*CONVERT(INT,
> ICFPM_USER_14)))
> END
> FROM SOFOD, ICFPM
> WHERE SOFOD.PART_ID = ICFPM.PART_ID AND SOFOD.SO_ID = '105706'
>

Divide by zero error! Help!

I don't understand why I get the error "Server: Msg 8134, Level 16,
State 1, Line 1
Divide by zero error encountered."
I check for 0, actually if I change the statement after ELSE to 2, it
will run with no issue and get 1 since the when statement is 0 in this
case. Please help.
SELECT
CASE WHEN SUM(CONVERT(INT, ICFPM_USER_12)* CONVERT(INT,
ICFPM_USER_14))= 0 THEN 1
ELSE SUM(REV_ORDER_QTY/(CONVERT(INT, ICFPM_USER_12)*CONVERT(INT,
ICFPM_USER_14)))
END
FROM SOFOD, ICFPM
WHERE SOFOD.PART_ID = ICFPM.PART_ID AND SOFOD.SO_ID = '105706'Hi
Don't assume that the execution order of an ELSE will follow the order you
coded. It may evaluate both CASEs in parallel and then use the output later.
During query execution, your query may be run differently to what you think.
This can be influenced by number of processors, RAM available, indexes and
statistics.
You need to make sure that your query does not have the possibility of
failing, no matter the code execution path.
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
<zod91@.yahoo.com> wrote in message
news:1149560228.320495.202850@.j55g2000cwa.googlegroups.com...
>I don't understand why I get the error "Server: Msg 8134, Level 16,
> State 1, Line 1
> Divide by zero error encountered."
> I check for 0, actually if I change the statement after ELSE to 2, it
> will run with no issue and get 1 since the when statement is 0 in this
> case. Please help.
>
> SELECT
> CASE WHEN SUM(CONVERT(INT, ICFPM_USER_12)* CONVERT(INT,
> ICFPM_USER_14))= 0 THEN 1
> ELSE SUM(REV_ORDER_QTY/(CONVERT(INT, ICFPM_USER_12)*CONVERT(INT,
> ICFPM_USER_14)))
> END
> FROM SOFOD, ICFPM
> WHERE SOFOD.PART_ID = ICFPM.PART_ID AND SOFOD.SO_ID = '105706'
>