SQL Formula Error - 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

SQL Formula Error

Author

Topic

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-03-31 : 06:12:49

SELECT ID, CalibScoreAVGQA,
COUNT(CalibScoreAVGQA) AS Expr1
FROM dbo.TechPhonesCalibScoreAVGQA

Any idea why I'm getting "dbo.TechPhonesCalibScoreAVGQA.ID is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-31 : 06:15:26

without wanting to state the obvious... you're doing an aggregate function and ID is not in the group by clause

read about group by in books online

Em


SELECT ID, CalibScoreAVGQA,
COUNT(CalibScoreAVGQA) AS Expr1
FROM dbo.TechPhonesCalibScoreAVGQA
group by ID, CalibScoreAVGQA

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-31 : 06:15:39

I think SQL Server is pretty obvious in its error message. All those columns which are not part of aggregate function like count(), sum(0 etc should be part of GROUP BY clause.

SELECT ID, CalibScoreAVGQA,
COUNT(CalibScoreAVGQA) AS Expr1
FROM dbo.TechPhonesCalibScoreAVGQA
GROUP BY ID, CalibScoreAVGQA

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-03-31 : 06:31:43

Yes, I got that what if I want to get the average of the whole column CalibScoreAVGQA? With this formula, the average is computed per row. I want the whole average of a certain column.

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-03-31 : 06:32:54

Select 'Average',Avg(CalibScoreAVGQA)
from dbo.TechPhonesCalibScoreAVGQA

OR

Select Avg(CalibScoreAVGQA) as 'Average of CalibScoreAVGQA'
from dbo.TechPhonesCalibScoreAVGQA

--Remember this will not consider records with NULL value for
CalibScoreAVGQA

Prakash.P
The secret to creativity is knowing how to hide your sources!

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-03-31 : 06:38:59

Here's the results i've got:

ID CalibScoreAVGQA Average of CalibScoreAVGQA
1 0 0
2 1 1

What I need is the average of CalibScoreAVGQA (1+0/2).

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-03-31 : 07:15:01

quote:
Originally posted by cutiebo2t

Here's the results i've got:

ID CalibScoreAVGQA Average of CalibScoreAVGQA
1 0 0
2 1 1

What I need is the average of CalibScoreAVGQA (1+0/2).

When I said OR, i meant that you need execute any one of the above queries.

Both the queries are doing the same thing and displaying the results in a different way

Prakash.P
The secret to creativity is knowing how to hide your sources!

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-03-31 : 07:21:10

Yes, how can I get the average of the values in column CalibScoreAVGQA?

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-03-31 : 07:54:25

Select Avg(CalibScoreAVGQA) as 'Average of CalibScoreAVGQA'
from dbo.TechPhonesCalibScoreAVGQA

Prakash.P
The secret to creativity is knowing how to hide your sources!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources