average question - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

average question

Author

Topic

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-06-28 : 09:02:15

I have a tbl_statistieken
With:


id  id_game  id_player score
313   32     23        60
313   32     23        26
313   32     23        140
313   32     23        33

Now I want the average score of player 23.
Thats not so difficult but again a dart question

The score 60/3 = 20 average (divided thrue 3 because I trow 3 darts)
26/3 = 8.66
140/3 = 46.66
33/3 = 11

Total = 20 + 8.66 + 46.66 + 11 = 86.32 / 4(amount of record) = 21.58
You can also say 60 + 26 + 140 + 33 = 259 / 12 ( 4 record * 3 darts) = 21.58

So That is the value I want because the player trowd 21.58 point a dart. I hope I am clear enough this time.

Thanxx For the help

Cya
Bjorn

Cya
Bjorn

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-28 : 09:34:19

id id_game id_player score
313 32 23 60
313 32 23 26
313 32 23 140
313 32 23 33

select id, id_game, id_player, avg(score/3.0) as AvgPerDart
from tbl_statistieken
group by id, id_game, id_player

- Jeff

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-06-28 : 09:47:33

Oke this is allready a start but I get the average of every single row now. And I want the avarage of every row together.

Hope you know the solution to this one!

Thanxx
Bjorn

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-06-28 : 10:04:45

select id_player, avg(score/3.0) as AvgPerDart
from tbl_statistieken
group by id_player

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-28 : 10:06:54

Run the query and look at the results. it doesn't give the average for each row, just for each player. unless your sample data is not accurate.

i.e., if each row in your data doesn't have the same value in "ID" column (in your example, "313"), then exlcude that column from the SELECT and the GROUP BY:

select id_game, id_player, avg(score/3.0) as AvgPerDart
from tbl_statistieken
group by id_game, id_player

you can also get the average for each game, all players:

select id_game, avg(score/3.0) as AvgPerDart
from tbl_statistieken
group by id_game

and you can also get the average for each player, for all games:

select id_player, avg(score/3.0) as AvgPerDart
from tbl_statistieken
group by id_player

read more about GROUP BY and aggregate functions in books on-line.

- Jeff

Edited by - jsmith8858 on 06/28/2003 10:07:43

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-06-28 : 10:25:00

Great!!!

Yes I made a copy/past error:


id  id_game  id_player score
313   32     23        60
314   32     26        26
315   32     23        140
316   32     26        33
317   32     23        11
318   32     26        4
319   32     23        10
320   32     26        100
321   33     44        50
322   33     48        150
323   33     44        71
324   33     48        56

So I also have to add where id_game = [id_game_field] And id_player = [id_player_field].

Only one small question I dont want to view:
33,336333333
But
33,34

Thanxx
Bjorn

Nazim
A custom title

1408 Posts

Posted - 2003-06-28 : 11:57:50

Check for Round function in BOL.

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources