Average excluding a number - 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 2005 Forums

Transact-SQL (2005)

Average excluding a number

Author

Topic

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2010-05-26 : 10:51:58

I am trying to find a code for AVG excluding a number. I would like to get the an average of a column excluding all nines.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-26 : 10:54:20

Do you mean a column with only the digit 9 as value?
Use NULLIF() because AVG is ignoring NULL values:

select
avg(nullif(your_column,9)) as avgCol
from your_table

No, you're never too old to Yak'n'Roll if you're too young to die.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-05-26 : 11:07:38

erm. yeah I guess. Or


SELECT
    AVG([<Your_Column>]) 
FROM
    your_table
WHERE
    [<your_column>] <> 9

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-26 : 11:13:51

Yes that seems pretty simple but the OP wrote find a code for AVG excluding a number...
not the query

No, you're never too old to Yak'n'Roll if you're too young to die.

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2010-05-26 : 11:38:50

webfred and Transact Charlie,

Error Message: Operand data type char is invalid fro avg operator.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-26 : 11:41:27

quote:
Originally posted by sergeant_time

webfred and Transact Charlie,

Error Message: Operand data type char is invalid fro avg operator.

Isn't the error self explanatory? You are trying to get an average of a character field. If it contains just numbers, convert it to an integer.

Read up on CAST() and CONVERT() to do the conversion.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-05-26 : 11:42:19

Can you show us the data_types of the table?

Maybe post some sample data as well -- the column is obviously not a numeric type.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2010-05-26 : 11:56:48

SELECT QuestionOne AS Q1, QuestionTwo AS Q2, QuestionThree AS Q3, QuestionFour AS Q4, SurveyDate AS [Survey Date], Split
FROM hrc_survey_results
WHERE (SurveyDate >= @Startdate) AND (SurveyDate < DATEADD(d, 1, @Enddate)) AND (Split IN (@PILOTNAME)) AND (Split IN (8, 28, 19, 9, 43)) AND
(Split IS NOT NULL)
GROUP BY QuestionOne, QuestionTwo, QuestionThree, QuestionFour, SurveyDate, Split
ORDER BY [Survey Date]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-27 : 03:37:39

quote:
Originally posted by sergeant_time

SELECT QuestionOne AS Q1, QuestionTwo AS Q2, QuestionThree AS Q3, QuestionFour AS Q4, SurveyDate AS [Survey Date], Split
FROM hrc_survey_results
WHERE (SurveyDate >= @Startdate) AND (SurveyDate < DATEADD(d, 1, @Enddate)) AND (Split IN (@PILOTNAME)) AND (Split IN (8, 28, 19, 9, 43)) AND
(Split IS NOT NULL)
GROUP BY QuestionOne, QuestionTwo, QuestionThree, QuestionFour, SurveyDate, Split
ORDER BY [Survey Date]

Is this related to this topic?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources