Avg with simple query - 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 with simple query

Author

Topic

lucsky8
Posting Yak Master

105 Posts

Posted - 2008-07-24 : 13:21:05

Hi, i need to retreive how many people have a score of 55% and + and 55 and -

If i use these query

select DISTINCT (strMoyenne) from tblNotePolyvalente
where intSchoolId = 1331 and
strClasseName = 'Français I -10131'AND
NOT strMoyenne is NULL

It give 290 people!

To check how many did 55 and +
i use

select DISTINCT AVG(strMoyenne) from tblNotePolyvalente
where intSchoolId = 1331 and
strClasseName = 'Français I -10131'AND
strMoyenne >= 55 and
NOT strMoyenne is NULL

It give 70.8% base on 261 entry

For 55 and -
i use

select DISTINCT AVG(strMoyenne) from tblNotePolyvalente
where intSchoolId = 1331 and
strClasseName = 'Français I -10131'AND
strMoyenne < 55 and
NOT strMoyenne is NULL

It give me 43% base on 30 entry

70.8% + 43% = 113.8%

It should give 100%

Any idea what i am doing wrong?

Tks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-24 : 13:29:20

i think what you want is average of distinct records which is not equal to DISTINCT AVG(). try like this


select AVG(strMoyenne) from 
(SELECT DISTINCT strMoyenne from
tblNotePolyvalente 
where intSchoolId = 1331 and 
strClasseName = 'Français I -10131'AND
strMoyenne >= 55 and 
 strMoyenne is NOT NULL)t

and

select AVG(strMoyenne) 
from 
(SELECT DISTINCT strMoyenne
from tblNotePolyvalente 
where intSchoolId = 1331 and 
strClasseName = 'Français I -10131'AND
strMoyenne < 55 and 
NOT strMoyenne is NULL)t

lucsky8
Posting Yak Master

105 Posts

Posted - 2008-07-24 : 13:32:16

Hi tks for your relply!

I just try it and still give 72% for 55 and +
and 42% 55 and -

It still give 113%

should it give 100% in total?
or i am missgin something

I need to find % of people you did 55% and more
and 55% and less

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-24 : 13:40:20

quote:
Originally posted by lucsky8

Hi tks for your relply!

I just try it and still give 72% for 55 and +
and 42% 55 and -

It still give 113%

should it give 100% in total?
or i am missgin something

I need to find % of people you did 55% and more
and 55% and less

so you are looking at %. then why are taking average of score?
if you want % of records with 55 + and 55- this is what you should do

select 
COUNT(DISTINCT CASE WHEN strMoyenne>= 55 THEN strMoyenne ELSE NULL END)*100.0/NULLIF(COUNT(DISTINCT strMoyenne),0) AS [55+],
COUNT(DISTINCT CASE WHEN strMoyenne< 55 THEN strMoyenne ELSE NULL END)*100.0/NULLIF(COUNT(DISTINCT strMoyenne),0) AS [55-]
from tblNotePolyvalente 
where intSchoolId = 1331 and 
strClasseName = 'Français I -10131'AND
strMoyenne is NOT NULL

lucsky8
Posting Yak Master

105 Posts

Posted - 2008-07-25 : 06:54:12

Your a genius tks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources