How do I select certain columns?

[Floodster]

Hi,
My scenario is that I have a table like below

Name | Game1 | Game2 | Game3 | Game4 | Game 5
A | 20 | 25 | 35 | 12 | 40

What I want to be able to do is work out the average based on the best 3 scoring games? The other problem is that there may be cases where not everyone has played 3 games.

So I would expect my query to only select Game2, Game3 & Game4 & then work out the average which would be 33.3.

Thing is I don't know how to do this, can anyone out there help??
I know it's late on Friday & England are playing tomorrow but I've been struggling for most of the day with this!!

Thanks,

 

[rac2]

That is going to be pretty difficult to do with the games represented as columns in a table. I cannot think of a way to do it.

But if you re-arrange things so that each game is a row in a table, then it is possible.

Name Game Score
Joe 1 20
Joe 2 25
Joe 3 35
Joe 4 12
Joe 5 40
Bob 3 29
Bob 5 39

SELECT a.Name, AVG(a.Score)
FROM (
       SELECT Name, Game, Score
       FROM GamesTable
       WHERE Name = 'Joe'
       ORDER BY Score
       LIMIT 3 ) a
GROUP BY a.Name
HAVING COUNT(*) > 2

This uses a subquery to obtain the highest 3 scores for Joe.
Then the outer query calculates the average but shows it only if Joe played 3 or more games.

This can probably be extended to produce results for each player but that will be more complicated and might require that you have a separate table listing each player once.


Good luck to England.

 

[johnwm]

Probably worth reading Fundamentals of Relational Database design by Paul Letwin. It's not as heavy as it sounds, and it may help you sort out how to plan your database design efficiently. It's a free download from

http://www.alphabetcitydataworks.com/fundamentals.htm

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[Floodster]

Cheers Guys,

If I re-arrange my tables as you mentioned how would I append the existing data into the new table.

So I would want to append the name & game 1 into the new table & then name & game 2 below name & game 1 & then repeat up to game 4.

Otherwise I assume i would i am going to have to input it manually??

Thanks.

 

[Floodster]

guys,
ignore the last, I have figured it out by using;

INSERT INTO newtable (blah, blah) SELECT blah, blah FROM oldtable;

Thanks for all the other tips.

 

[Floodster]

RAC2, Looking at your code again I will need to produce the results for all the players so I wouldn't be able to specify 'WHERE Name ="JOE"'.

Any ideas how I would do this??

 

[hoinz]

This question was answered a few days ago.
have a look at thread436-1245865