AVG not showing the right results

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

General SQL Server Forums

New to SQL Server Programming

AVG not showing the right results

Author

Topic

bulubuk1976
Starting Member

24 Posts

Posted - 2008-03-29 : 04:01:25

Here's my code

View Table 3:

SELECT dbo.VIEW1Client.ID, dbo.VIEW1QA.Score, dbo.VIEW1Client.Score AS Score2, CASE WHEN dbo.VIEW1Client.Score = dbo.VIEW1QA.Score THEN 1 ELSE 0 END) AS Calibrate
FROM dbo.VIEW1QA CROSS JOIN
dbo.VIEW1Client
WHERE dbo.VIEW1Client.ID = '1'

View Table 4: (I created a view table using View Table 3 to get the average and it seems not providig the right average, instead I get 0:

SELECT ID, AVG(Calibrate) AS Perc
FROM dbo.VIEW3
GROUP BY ID

Any Ideas please?

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-03-29 : 07:13:39

Could you confirm that when you run the the first statement (View Table 3) that you are getting some results in "Calibrate" that are greater than 0?

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

bulubuk1976
Starting Member

24 Posts

Posted - 2008-03-29 : 07:26:15

quote:
Originally posted by jackv

Could you confirm that when you run the the first statement (View Table 3) that you are getting some results in "Calibrate" that are greater than 0?

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

Yes, I get 5 1s and 1 0s.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-03-29 : 08:32:26

your "calibrate" datatype is working out as INT. You need to set it as some flavor of decimal.


declare @t table (i int)
insert @t
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 0

select * from @t

select avg(i) from @t

select avg( convert(money, i)) from @t

output:
i
-----------
1
1
1
1
1
0


---------------------
0.00


---------------------
0.8333

Be One with the Optimizer
TG

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-03-29 : 10:42:19

Sir Dens,

Try this:

SELECT dbo.VIEW1Client.ID, dbo.VIEW1QA.Score, dbo.VIEW1Client.Score AS Score2, CASE WHEN dbo.VIEW1Client.Score = dbo.VIEW1QA.Score THEN 1.0 ELSE 0.0 END) AS Calibrate
FROM dbo.VIEW1QA CROSS JOIN
dbo.VIEW1Client
WHERE dbo.VIEW1Client.ID = '1'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources