quote:

---

Originally posted by vijays3

Like this

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

---

quote:

---

Originally posted by sparro

I am using MySQL. Thanks.

---

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

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/

---

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

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/

---

---

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/

---

---