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.
| Author |
Topic |
|
behringer420
Starting Member
2 Posts |
 Posted - 2010-03-11 : 17:20:54
|
| HiI have been asked to execute a statement that show's me the average number of students of a class. Now normally when I want to know how many students belong to one class I would do a simple GROUP BY and COUNT statement, however I'm not sure how I do the same but instead find out the AVG instead of finding out how many there actually are.Anyone have any ideas? |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-03-11 : 18:00:50
|
I would do it like this:select avg(studentscounter) as avg_number_of_studentsfrom(select class, count(*) as studentscounter from table group by class) as dtBut I am not sure if there are more elegant ways because I never needed this.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
behringer420
Starting Member
2 Posts |
Posted - 2010-03-13 : 17:32:45
|
| Hey, thanks that worked great.Ive just found out I need to make sure the students have a start_date and end_date that is between 1-jun-2010 to 30-jun-2010.how would i implement this into the statement above?thanks :) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-14 : 00:27:03
|
quote:
Originally posted by behringer420
Hey, thanks that worked great.Ive just found out I need to make sure the students have a start_date and end_date that is between 1-jun-2010 to 30-jun-2010.how would i implement this into the statement above?thanks :)
select avg(studentscounter) as avg_number_of_studentsfrom(select class, count(*) as studentscounter from table where start_date >= '20100601' and end_date <='20100630' group by class) as dt ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Joining a AVG and COUNT statement...