Division query

i have an equation which i have to implement in query:
availa= (1-[return0]/sum(r1+r2+r3+r4+r5))
when tried the query i get avail as zero always, help me outNAVIN.D skrev:

> i have an equation which i have to implement in query:
> availa= (1-[return0]/sum(r1+r2+r3+r4+r5))
> when tried the query i get avail as zero always, help me out
Without knowing what the above equation is or where it is used the easy
explanation would be that it's caused by integer division, ie.
everything below 1.0 will become 0.
/impslayer, aka Birger Johansson|||You will get an integer result when all operands are integer. You can
specify a decimal operand if you need decimal result. The result will
decimal in that case because the decimal data type has a higher precedence
than integer. Try:
availa = (1.0-[return0]/sum(r1+r2+r3+r4+r5))
Hope this helps.
Dan Guzman
SQL Server MVP
"NAVIN.D" <NAVIND@.discussions.microsoft.com> wrote in message
news:48C5F07C-1023-4AB1-9B17-356321EB95CD@.microsoft.com...
> i have an equation which i have to implement in query:
> availa= (1-[return0]/sum(r1+r2+r3+r4+r5))
> when tried the query i get avail as zero always, help me out|||If SUM(r1+r2+r3+r4+r5) > (1-[return0]), you will get zero because SQL will
return a value within the datatype integer. (The answer will be truncated).
Try
(1.00 - [return0]/sum(r1+r2+r3+r4+r5)
"NAVIN.D" wrote:

> i have an equation which i have to implement in query:
> availa= (1-[return0]/sum(r1+r2+r3+r4+r5))
> when tried the query i get avail as zero always, help me out|||thank you mark and imsplayer it was decimal and integer funda only i got it
immd after posting anyways thanks for the reply
"Mark Williams" wrote:
> If SUM(r1+r2+r3+r4+r5) > (1-[return0]), you will get zero because SQL will
> return a value within the datatype integer. (The answer will be truncated)
.
> Try
> (1.00 - [return0]/sum(r1+r2+r3+r4+r5)
>
> --
>
> "NAVIN.D" wrote:
>

Division by zero

Hi!

I have a field where i have to do a division. To be sure that the value will never be zero i have done this:

=IIf(Sum(Fields!Total_Amount_1.Value) <>0,
((Sum(Fields!Total_Amount_2.Value)-Sum(Fields!Total_Amount_1.Value)) / Abs(Sum(Fields!Total_Amount_1.Value))),"N/A")

When i run the report i get the "#Error" in the field.

I have done some test and notice that it seems that the problem is that the IIF try both condition (true condition and false condition). When i replace the ABS function by a number like 100 everything is working fine. So the error seems to be the division by zero but this is why i'm using a IIF.

How can i do this division only if the value is not 0 and when it's 0 return "N/A" ?

Thank !
I found a post with the same question as mine:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=188595&SiteID=1

This work fine!

Thanks

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 by 0

dear all,
hi, i got this problem - Divide by zero error encountered.

can someone please help me and this is the code

--cast(
Sum(Case
When Proj_Status = 'Pending' and m01.created >='2003-06-12' AND
m01.created <'2003-06-14' then 1
Else 0
End) * 100 /
Sum(Case
when m02.ID = m01.BoardID and m01.created >='2003-06-12' AND
m01.created <'2003-06-14' then 1
else 0
end)
--as numeric(3,2))

thank you very much indeed.

regards,
Catcycyou should drop a Case clause in which will execute in place of these when the value you're using is zero, OR you should filter the data so you don't get zeroes in the first place.|||Or if you just want to suppress the error, use

SET ARITHABORT ON | OFF|||dear Dutch,
i've use the codes that you recommended but still cannot generate the thing that i want.

this is my first time to generate report that calculate percentage by using sql statement & also the hard part is to check how many pending case within certain period as below code :

Sum(Case
When Proj_Status = 'Pending' and m01.created >='2003-06-12' AND
m01.created <'2003-06-14' then 1
Else 0
End) * 100 /
Sum(Case
when m02.ID = m01.BoardID and m01.created >='2003-06-12' AND
m01.created <'2003-06-14' then 1
else 0
end)

i was thinking can i use ifelse statement to check before the above code to prevent this error. But i'm not really familiar with the usage of if else statement in sql.

so, can you teach me how to use or anyone can show me.

regards,
Catcyc