AVG not working - 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

General SQL Server Forums

New to SQL Server Programming

AVG not working

Author

Topic

bulubuk1976
Starting Member

24 Posts

Posted - 2008-03-28 : 21:17:01

I would like to get the average of a field but I am not sure if I am doing it correctly.

SELECT dbo.VIEW2.Den AS Den2, CASE WHEN VIEW1.Den = VIEW2.Den THEN 1 ELSE 0 END AS Den3, dbo.VIEW2.ID, AVG(dbo.VIEW2.Den) AS Den4
FROM dbo.VIEW1 CROSS JOIN
dbo.VIEW2
WHERE (dbo.VIEW2.ID = '1')

Here is the error I am getting: Column dbo.View2.Den is invalid in the SELECT list because it is not an aggregate function and there is no Group BY CLause.

Thanks for the help

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-28 : 21:22:15

SELECT dbo.VIEW2.Den AS Den2, CASE WHEN VIEW1.Den = VIEW2.Den THEN 1 ELSE 0 END AS Den3, dbo.VIEW2.ID, AVG(dbo.VIEW2.Den) AS Den4
FROM dbo.VIEW1 CROSS JOIN
dbo.VIEW2
WHERE (dbo.VIEW2.ID = '1')
group by dbo.VIEW2.Den ,dbo.VIEW2.ID

bulubuk1976
Starting Member

24 Posts

Posted - 2008-03-28 : 21:37:52

quote:
Originally posted by sodeep

SELECT dbo.VIEW2.Den AS Den2, CASE WHEN VIEW1.Den = VIEW2.Den THEN 1 ELSE 0 END AS Den3, dbo.VIEW2.ID, AVG(dbo.VIEW2.Den) AS Den4
FROM dbo.VIEW1 CROSS JOIN
dbo.VIEW2
WHERE (dbo.VIEW2.ID = '1')
group by dbo.VIEW2.Den ,dbo.VIEW2.ID

I am still getting the same error after I copy and pasted your code. Anymore ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-29 : 01:57:31

SELECT dbo.VIEW2.Den AS Den2, CASE WHEN VIEW1.Den = VIEW2.Den THEN 1 ELSE 0 END AS Den3, dbo.VIEW2.ID, t.AvgDen AS Den4
FROM dbo.VIEW1 CROSS JOIN
dbo.VIEW2
INNER JOIN (SELECT ID,AVG(Den) AS AvgDen
FROM VIEW2
GROUP BY ID) t
ON t.ID=dbo.VIEW2.ID
WHERE (dbo.VIEW2.ID = '1')
group by dbo.VIEW2.Den ,dbo.VIEW2.ID

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-02 : 05:40:23

You may need to use 1.0 instead of 1

Madhivanan

Failing to plan is Planning to fail

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-02 : 05:43:09

quote:
Originally posted by madhivanan

You may need to use 1.0 instead of 1

Why?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-02 : 06:09:46

quote:
Originally posted by nr

quote:
Originally posted by madhivanan

You may need to use 1.0 instead of 1

Why?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

No need in this case

I thought AVG is used over CASE WHEN expression

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources