AVG() aggregate with CASE

i have 2 columns of type decimal that are divided and rounded and
return zero if the divisor is zero. it all works fine until i try to
AVG() them. now it seems my CASE to avoid dividing by zero is no
longer working...or i'm just approaching it all the wrong way. my
brain is a bit fried from staying up late to meet a deadline and i was
wondering if someone could give some advice.
SELECT CASE a.Value2 WHEN 0 THEN 0 ELSE ROUND((b.Value1 / b.Value2 *
1000) * 100, 2, 1) END AS Rating FROM a INNER JOIN b ON a.ID = b.ID
b.Value1 = 101664
a.Value2 = 7730
Rating = 1.31
i now try putting the AVG() function to work like this:
SELECT CASE a.Value2 WHEN 0 THEN 0 ELSE AVG( ROUND((b.Value1 / b.Value2
* 1000) * 100, 2, 1) ) END AS Rating FROM a INNER JOIN b ON a.ID =
b.ID GROUP BY a.Value2
if i have 5 rows of results .96, .97, .98, 1.13, 1.31, then it works i
get one record with the Rating = 1.07. yay!
the problem comes in where sometimes, the Values are 0. before using
the AVG() function, the CASE took care of these columns so that my
Rating results would be 0. but now i keep getting a "Divide by zero
error encountered". if the CASE is working, it shouldn't even try the
AVG() calculation right? or am i approaching this the wrong way? and
no, i can't use the SET ANSI_WARNINGS OFF because we're not using
stored procedures in this case.
thanks for any input or insight you may provide!
susieWhy not avoid the 0 problem by putting in a WHERE clause? ie
WHERE value1 > 0
AND value2 > 0
If that's not going to work, post some more sample values for tables a and b
.
Thanks
Damien
"TuBuGuRL" wrote:

> i have 2 columns of type decimal that are divided and rounded and
> return zero if the divisor is zero. it all works fine until i try to
> AVG() them. now it seems my CASE to avoid dividing by zero is no
> longer working...or i'm just approaching it all the wrong way. my
> brain is a bit fried from staying up late to meet a deadline and i was
> wondering if someone could give some advice.
> SELECT CASE a.Value2 WHEN 0 THEN 0 ELSE ROUND((b.Value1 / b.Value2 *
> 1000) * 100, 2, 1) END AS Rating FROM a INNER JOIN b ON a.ID = b.ID
> b.Value1 = 101664
> a.Value2 = 7730
> Rating = 1.31
> i now try putting the AVG() function to work like this:
> SELECT CASE a.Value2 WHEN 0 THEN 0 ELSE AVG( ROUND((b.Value1 / b.Value2
> * 1000) * 100, 2, 1) ) END AS Rating FROM a INNER JOIN b ON a.ID =
> b.ID GROUP BY a.Value2
> if i have 5 rows of results .96, .97, .98, 1.13, 1.31, then it works i
> get one record with the Rating = 1.07. yay!
> the problem comes in where sometimes, the Values are 0. before using
> the AVG() function, the CASE took care of these columns so that my
> Rating results would be 0. but now i keep getting a "Divide by zero
> error encountered". if the CASE is working, it shouldn't even try the
> AVG() calculation right? or am i approaching this the wrong way? and
> no, i can't use the SET ANSI_WARNINGS OFF because we're not using
> stored procedures in this case.
> thanks for any input or insight you may provide!
> susie
>|||hi damien,
thanks for the reply. the WHERE clause is a good idea, but i don't
want to exclude these values, i need them to show up as averaged 0 in
my report.
i just realized that in my post above it's supposed to say "... ROUND(
(b.Value1 / a.Value2) ) ..." NOT "... ROUND( (b.Value1 / b.Value2) )
..."
anyway more sample data:
a.Value2 = 5910
b.Value1 = 120779
Rating = 2.04
5 different Ratings: 2.04, 1.01, 0.98, 0.91, 0.41
Avg() rating = 1.07
data used when i receive the error:
a.Value2 = 0
b.Value2 = 0
Rating = .0000000000000000
5 different Ratings: all .0000000000000000
shouldn't the CASE i use in the SELECT avoid even trying to AVG() the 0
Ratings?
thanks,
susie|||Put the CASE inside the AVG
...
AVG(CASE a.Value2 WHEN 0 THEN 0 ELSE ROUND(...) END)
...
TuBuGuRL wrote:
> i have 2 columns of type decimal that are divided and rounded and
> return zero if the divisor is zero. it all works fine until i try to
> AVG() them. now it seems my CASE to avoid dividing by zero is no
> longer working...or i'm just approaching it all the wrong way. my
> brain is a bit fried from staying up late to meet a deadline and i was
> wondering if someone could give some advice.
> SELECT CASE a.Value2 WHEN 0 THEN 0 ELSE ROUND((b.Value1 / b.Value2 *
> 1000) * 100, 2, 1) END AS Rating FROM a INNER JOIN b ON a.ID = b.ID
> b.Value1 = 101664
> a.Value2 = 7730
> Rating = 1.31
> i now try putting the AVG() function to work like this:
> SELECT CASE a.Value2 WHEN 0 THEN 0 ELSE AVG( ROUND((b.Value1 / b.Value2
> * 1000) * 100, 2, 1) ) END AS Rating FROM a INNER JOIN b ON a.ID =
> b.ID GROUP BY a.Value2
> if i have 5 rows of results .96, .97, .98, 1.13, 1.31, then it works i
> get one record with the Rating = 1.07. yay!
> the problem comes in where sometimes, the Values are 0. before using
> the AVG() function, the CASE took care of these columns so that my
> Rating results would be 0. but now i keep getting a "Divide by zero
> error encountered". if the CASE is working, it shouldn't even try the
> AVG() calculation right? or am i approaching this the wrong way? and
> no, i can't use the SET ANSI_WARNINGS OFF because we're not using
> stored procedures in this case.
> thanks for any input or insight you may provide!
> susie
>

AVG of integer column returns integers result - newbie

I am averaging integer columns, the result is integer not real or decimal
How do I get aroung this?
SELECT AVG(AmpReading)
FROM tblChillWaterSystems
GROUP BY LocationID, SystemID
Values are
1
3
6
--
10
10/3 = 3.33 but the result is 3SELECT AVG(CAST(AmpReading AS REAL)) ...
--
David Portas
SQL Server MVP
--|||Try,
SELECT AVG(AmpReading * 1.00)
FROM tblChillWaterSystems
GROUP BY LocationID, SystemID
or cast [AmpReading] to numeric.
AMB
"Craig" wrote:
> I am averaging integer columns, the result is integer not real or decimal
> How do I get aroung this?
> SELECT AVG(AmpReading)
> FROM tblChillWaterSystems
> GROUP BY LocationID, SystemID
> Values are
> 1
> 3
> 6
> --
> 10
> 10/3 = 3.33 but the result is 3
>
>

Average

I have a field in my database for the duration of a phonecall. It is input as decimal values of minutes and i have it stored as real datatype... When I use an SQL query to calculate the average i get an in accurate answer... I'm new to databases and don't really know what datatype i should store it as... does anyone have any suggestions?a real data type should work fine, are you tring to store a base 60 number in a base 10 data type? why not convert the decimal into number of seconds (if you need that much resolution) and store the result in an int? Maybe an example would help.|||I'm importing values from a text file into the database. The values in the text files are simple decimals... eg. 0.1, 28.78... and are imported into the db as the same.

Let me try to explain. Its a database that stores the duration of a phone call and the number of the phone from which it is made amongst other fields. One of the phone numbers made two outgoing calls, both 0.1 minutes long. I wrote the query, select avg(duration) .... and it reurned the value 0.100000001490116 for the average!!!

I hope you can shed some light on this for me because as I said, I'm relatively new to databases and I don't know where the problem lies!

Thanks...|||DING DING DING

Float and Real are approximate number data types. What you got was approximately 0.1. Change your data type to Decimal or Numeric probably Decimal(9,4) would work.|||Thanks for you help so far...

Pardon my ignorance but what do you mean by (9,4) in decimal (9,4)?|||from Books Online:

decimal and numeric
Numeric data types with fixed precision and scale.

decimal[(p[, s])] and numeric[(p[, s])]

Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The SQL-92 synonyms for decimal are dec and dec(p, s).

p (precision)

Specifies the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision. The maximum precision is 38. The default precision is 18.

s (scale)

Specifies the maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.|||Thanks a million Paul... thats a great help...