Get an average of a column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Get an average of a column

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2007-10-30 : 06:09:16

I am trying to get a simple average of 'PublOffers' column which is smallint data type, but I get always zero value as average, and this is not correct since there are values different to zero in that column for the specified month and year. Somebody knows which can be de problem?

This is the simple code I use:


    SELECT AVG(PublOffers) As myAverage
    FROM PublOffersControl 
    WHERE MONTH(Data) = 3 And YEAR(Data) = 2006

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-30 : 06:20:34

Please post the sample data.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

cesark
Posting Yak Master

215 Posts

Posted - 2007-10-30 : 06:46:49

If I use this Select:


    SELECT User_num As User, sum(PublOffers) as Offers, avg(PublOffers) As myAverage
                   
    FROM PublOffersControl 
    WHERE MONTH(Data) = 3 And YEAR(Data) = 2006 
    GROUP BY User_num

I get this data:

User Offers myAverage
284 0 0
299 0 0
246 0 0
285 0 0
260 0 0
331 0 0
316 0 0
277 4 0
323 1 0
292 0 0
339 0 0
300 0 0
332 0 0
232 0 0
317 0 0
324 0 0
340 0 0
262 0 0
333 3 0
325 0 0
318 0 0
288 5 0
326 0 0
319 0 0
342 0 0
312 0 0
335 0 0
281 0 0
327 0 0
304 0 0
343 0 0
282 3 0
305 0 0
236 0 0
259 0 0
328 0 0
344 2 0
289 0 0
283 0 0
314 0 0
275 0 0
337 0 0
345 0 0

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-10-30 : 07:21:56

Try
1.0 * AVG(PublOffers)

cesark
Posting Yak Master

215 Posts

Posted - 2007-10-30 : 07:35:57

Hi, if I do this I get '.0' instead of '0'. It's still incorrect

Kristen
Test

22859 Posts

Posted - 2007-10-30 : 07:50:56

" Try
1.0 * AVG(PublOffers)"

I think you need

AVG(PublOffers * 1.0)

to increase the precision, or perhaps:

AVG(CONVERT(float, PublOffers))

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-30 : 08:23:09

Some operations involved on INT column(division, average, etc) need the column converted to float or multiplied by 1.0. I wonder why there is no implicit conversion on these operations(as done by other RDBMSs)

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-30 : 08:50:41

quote:
Originally posted by madhivanan

Some operations involved on INT column(division, average, etc) need the column converted to float or multiplied by 1.0. I wonder why there is no implicit conversion on these operations(as done by other RDBMSs)

Madhivanan

Failing to plan is Planning to fail

I think that's a good thing. If I perform an operation on two integers, an integer should be returned unless I explicitly ask for something else.

- Jeff
http://weblogs.sqlteam.com/JeffS

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-30 : 09:34:52

quote:
Originally posted by jsmith8858

quote:
Originally posted by madhivanan

Some operations involved on INT column(division, average, etc) need the column converted to float or multiplied by 1.0. I wonder why there is no implicit conversion on these operations(as done by other RDBMSs)

Madhivanan

Failing to plan is Planning to fail

I think that's a good thing. If I perform an operation on two integers, an integer should be returned unless I explicitly ask for something else.

- Jeff
http://weblogs.sqlteam.com/JeffS

Yes. SQL Server uniquely stands with some features like this

Also, In SQL Server, unique column will have only one NULL whereas other RDMSs allow more than one NULLs (Colleagues used to debate on this

)

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-10-30 : 10:24:11

And I've made views WHERE MyColumn IS NOT NULL and then indexed on that - and then discovered there were undesirable side effects of that, so then implemented trigger to enforce uniqueness of not-null column values instead ...

Kristen

cesark
Posting Yak Master

215 Posts

Posted - 2007-10-30 : 10:58:35

I can't still discuss about this matter

, but it works!!

I converted to float as Kristen said, and a very precision number is returned (this time the correct average). If I use 'AVG(PublOffers * 1.0)' only a few digits are returned after the decimal point, I suppose it's a question of choice or kind of use of that result.

Thank you!!

Kristen
Test

22859 Posts

Posted - 2007-10-30 : 11:36:23

"I suppose it's a question of choice or kind of use of that result"

Yup. You could use

SELECT ..., ROUND(avg(PublOffers), nnnn) As myAverageROUND ...

if you want to reduce the precision after the average is calculated

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-30 : 13:42:06

If you have an index on column Data, you should try out this WHERE clause

SELECT AVG(PublOffers) As myAverage
FROM PublOffersControl
WHERE Data >= '20060301' AND Data < '20060401'

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources