Click here to Skip to main content
15,867,141 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
Hi friends,
I have two tables as follows :

Table_1

ID    Name
1     AAA
2     BBB
3     ccc


Table_2

ID   Sub   Marks
1    M1    50
1    m2    65
2    M1    89
2    M2    93
3    M1    89
3    M2    56


I need to get average of Marks and ID having highest avg will be on top.

somthing like ..

ID Name     Avg
2  BBB      91
3  CCC      72.5
1  AAA      57.5


Any idea how can I get this??

Any help appreciated.
Thanks,
Lok..
Posted
Updated 5-Apr-12 0:53am
v2
Comments
kishore sharma 5-Apr-12 7:23am    
I Could Not Find Any Relation Between Table_1 & Table_2.

Try this:
SQL
SELECT DISTINCT Id, Name, AVG(Marks) as Avg
FROM Table_2
JOIN Table_1 ON Table_2.ID = Table_1.ID
ORDER BY Avg
 
Share this answer
 
v4
Comments
P.Salini 5-Apr-12 7:37am    
With this query i don't think you will get Name
check once
Prasad_Kulkarni 5-Apr-12 7:44am    
My apologize;
I have corrected it.
Try:
SQL
SELECT t2.ID, t1.Name, AVG(t2.Marks) AS Average
  FROM Table_2 t2 
  JOIN Table_1 t1 ON t1.ID = t2.ID 
  GROUP BY t2.ID , t1.Name
  ORDER BY Average DESC
 
Share this answer
 
Comments
Lokesh Zende 6-Apr-12 1:57am    
Thanks OriginalGriff ...
:)
OriginalGriff 6-Apr-12 2:17am    
You're welcome!
Monjurul Habib 6-Apr-12 5:14am    
5!
try to this link www.w3schools.com/[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900