average calculation - 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

average calculation

Author

Topic

sparro
Starting Member

6 Posts

Posted - 2012-04-28 : 20:59:12

let us say that in one table, there are student groups a b c.
they have test scores in one column and names in another.
how can i compute average scores in each student group? thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-28 : 21:51:09

its straight forward. Apply GROUP BY over studentgroup column and then apply AVG() function over the test score column.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-04-29 : 01:33:20

Like this

Select groupCol ,avg(score) from table group by groupCol

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-29 : 02:32:25

This is of course if the Score column is numeric. If you have an "A-F" value for score, you will need to convert the character into a number.

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-29 : 08:53:01

quote:
Originally posted by vijays3

Like this

Select groupCol ,avg(score *1.0) from table group by groupCol

if score data type is integer and you want decimal average then use the small modification as shown above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sparro
Starting Member

6 Posts

Posted - 2012-04-29 : 12:10:37

Thank you!

In the given example, what should I do if I want to list scores of students whose scores are lower than average of their own group (not of the whole)?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-29 : 12:29:38

are you using sql 2005 or above?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sparro
Starting Member

6 Posts

Posted - 2012-04-29 : 12:32:35

I am using MySQL. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-29 : 12:43:44

quote:
Originally posted by sparro

I am using MySQL. Thanks.

then you should be posting in MySQL forums
this is ms sql server forum and solutions given here are guaranteed to work only in sql server

In sql server you can do it like


SELECT t.*
FROM table t
INNER JOIN (SELECT StudentGroup,AVG(score*1.0) AS AvgScore
            FROM table
            GROUP BY StudentGroup
            )t1
ON t1.StudentGroup = t.StudentGroup
WHERE t.score < t1.AvgScore

not sure this works in MySQL (ideally it should as its ANSI compatible)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sparro
Starting Member

6 Posts

Posted - 2012-04-29 : 13:03:38

Hello,

Below are two tables. In one, there are scores of students. In other, it shows which group students belongs to.

How can I write query to list students and scores if their scores are lower or equal to the average of their own student group? (Please note one student does not have the score.)

thank you much!!

Student Table

Stuednt_ID Student_Name Score
1 alice 50
2 beth 60
3 cathy 80
4 david 90
5 ed 96
6 fay

Group Table

Stuednt_ID Student_Group Score
1 A
2 A
3 A
4 B
5 B
6 A

sparro
Starting Member

6 Posts

Posted - 2012-04-29 : 13:54:37

Sorry but...

what is

t1 (2nd tsble?)
avg(score*1.0) ???
t.score (1st table score?)
t1.avgscore (1st table average score?)

what happens if one person does not have score?

thanks.

quote:
Originally posted by visakh16

quote:
Originally posted by sparro

I am using MySQL. Thanks.

then you should be posting in MySQL forums
this is ms sql server forum and solutions given here are guaranteed to work only in sql server

In sql server you can do it like

SELECT t.*
FROM table t
INNER JOIN (SELECT StudentGroup,AVG(score*1.0) AS AvgScore
FROM table
GROUP BY StudentGroup
)t1
ON t1.StudentGroup = t.StudentGroup
WHERE t.score < t1.AvgScore

not sure this works in MySQL (ideally it should as its ANSI compatible)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-04-29 : 18:22:58

quote:
Originally posted by sparro

Hello,

Below are two tables. In one, there are scores of students. In other, it shows which group students belongs to.

How can I write query to list students and scores if their scores are lower or equal to the average of their own student group? (Please note one student does not have the score.)

thank you much!!

Student Table

Stuednt_ID Student_Name Score
1 alice 50
2 beth 60
3 cathy 80
4 david 90
5 ed 96
6 fay

Group Table

Stuednt_ID Student_Group Score
1 A
2 A
3 A
4 B
5 B
6 A


select a.student_id
      ,a.student_name
      ,b.student_group
      ,b.score
      ,c.avg_score
  from student_table as a
       inner join group_table as b
               on b.student_id=a.student_id
       inner join (select student_group
                         ,avg(score) as avg_score
                     from student_group
                    group by student_group
                  ) as c
               on b.student_group=b.student_group
 where b.score<=c.avg_score

This has not been tested in any way, so there might be type/syntax errors.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-04-29 : 18:27:49

quote:
Originally posted by sparro

Sorry but...

what is

t1 (2nd tsble?)
avg(score*1.0) ???
t.score (1st table score?)
t1.avgscore (1st table average score?)

what happens if one person does not have score?

thanks.

quote:
Originally posted by visakh16

quote:
Originally posted by sparro

I am using MySQL. Thanks.

then you should be posting in MySQL forums
this is ms sql server forum and solutions given here are guaranteed to work only in sql server

In sql server you can do it like

SELECT t.*
FROM table t
INNER JOIN (SELECT StudentGroup,AVG(score*1.0) AS AvgScore
FROM table
GROUP BY StudentGroup
)t1
ON t1.StudentGroup = t.StudentGroup
WHERE t.score < t1.AvgScore

not sure this works in MySQL (ideally it should as its ANSI compatible)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

- t and t1 would be table name aliases.
- multiplying score by 1.0 would be to get a float value returned.
- avgscore is the column alias given by subselect, containing the calculation AVG(score*1.0)

Hope this helps

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-29 : 19:01:30

quote:
Originally posted by sparro

Sorry but...

what is

t1 (2nd tsble?)
avg(score*1.0) ???
t.score (1st table score?)
t1.avgscore (1st table average score?)

what happens if one person does not have score?

thanks.

quote:
Originally posted by visakh16

quote:
Originally posted by sparro

I am using MySQL. Thanks.

then you should be posting in MySQL forums
this is ms sql server forum and solutions given here are guaranteed to work only in sql server

In sql server you can do it like

SELECT t.*
FROM table t
INNER JOIN (SELECT StudentGroup,AVG(score*1.0) AS AvgScore
FROM table
GROUP BY StudentGroup
)t1
ON t1.StudentGroup = t.StudentGroup
WHERE t.score < t1.AvgScore

not sure this works in MySQL (ideally it should as its ANSI compatible)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

t and t1 are aliases ie short names for tables
if person doesnt have score the record wont appear

b/w did this work in mysql?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sparro
Starting Member

6 Posts

Posted - 2012-04-30 : 12:29:02

thank you. yes, i am trying on mysql. thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-30 : 14:26:49

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources