Remove duplicate rows from joined table

Question

I have following sql query

SELECT m.School, c.avgscore 
FROM postswithratings c 
join ZEntrycriteria on c.fk_postID= m.schoolcode 

Which provide following result

School| avgscore
xyz   |  5
xyz   |  5
xyz   |  5
abc   |  3
abc   |  3
kkk   |  1

My question is how to remove those duplicates and get only following.

 School| avgscore
    xyz   |  5 
    abc   |  3
    kkk   |  1

I tried with

   SELECT m.School, c.avgscore 
   FROM postswithratings c 
   join ZEntrycriteria on c.fk_postID= m.schoolcode 
   group by m.School 

But it gives me following error

"Column 'postswithratings.avgscore' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

Answer 1

No need to make things complicated. Just go with:

SELECT m.School, c.avgscore 
FROM postswithratings c 
join ZEntrycriteria on c.fk_postID= m.schoolcode 
group by m.School, c.avgscore 

or

SELECT DISTINCT m.School, c.avgscore 
FROM postswithratings c 
join ZEntrycriteria on c.fk_postID= m.schoolcode 

Answer 2

You have to only add distinct keyword like this :-

 SELECT DISTINCT  m.School, c.avgscore 
  FROM postswithratings c 
 join ZEntrycriteria on c.fk_postID= m.schoolcode