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

Author  Topic 

BankOfficerHere
Posting Yak Master

124 Posts
Posted - 2008-08-15 : 10:08:26
Here's my table:

ID Name Scores
1 Michael 25
2 Allan 23
3 Michelle 56
4 Allan 23
5 Michael 45
6 Michelle 34

I want to get each name's average scores that would be resulted to this:

Name Scores
Michael 35
Allan 23
Michelle 45

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-08-15 : 10:12:44
SELECT Name, AVG(Scores)
FROM Table1
GROUP BY Name


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

BankOfficerHere
Posting Yak Master

124 Posts
Posted - 2008-08-15 : 10:16:30
Thanks Peso. Is it okay if I ask another Question. I would like it in a descending order using ID. Is that possible?
Go to Top of Page

Van
Constraint Violating Yak Guru

462 Posts
Posted - 2008-08-15 : 10:20:06
Post the results exatly as you would like to see them in this example please (like you did in the first question).
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-08-15 : 10:20:22
SELECT Name, AVG(Scores)
FROM Table1
GROUP BY Name
ORDER BY MIN(ID)

E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

BankOfficerHere
Posting Yak Master

124 Posts
Posted - 2008-08-15 : 10:33:15
Sorry for asking again as I'm a newbie to this. I forgot to include another column.

ID Employee Id Name Scores
1 222 Michael 34
2 333 Allan 54
3 444 Michelle 34
4 222 Allan 45
5 222 Michael 45
6 444 Michelle 23


I want the results to be like this

EmployeeID Name Scores
222 Michael 35
333 Allan 23
444 Michelle 45
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-08-15 : 10:39:18
Are you using SQL Server 2000 or SQL Server 2005?


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

BankOfficerHere
Posting Yak Master

124 Posts
Posted - 2008-08-15 : 10:40:14
2000
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-08-15 : 10:40:45
SELECT MIN(EmployeeID), Name, AVG(Scores)
FROM Table1
GROUP BY Name
ORDER BY MIN(EmployeeID)

E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

BankOfficerHere
Posting Yak Master

124 Posts
Posted - 2008-08-15 : 10:47:13
thanks peso..you've been a big help.
Go to Top of Page

Van
Constraint Violating Yak Guru

462 Posts
Posted - 2008-08-15 : 11:00:43
What if two people have the same name but different employee_id's?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-08-15 : 11:18:37
[code]SELECT EmployeeID,
Name,
AVG(Scores)
FROM Table1
GROUP BY Name,
EmployeeID
ORDER BY EmployeeID[/code]

E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site