Grouping inside sub-queary

railes · #1 (**permalink**) 05-08-09, 11:55

Hi,

I have a problem which is driving me nuts and I just can't see how to get the answer. I would really appreciate it if somebody could even point me in the right direction.

I have a simple db that store details of a game. A game consists of rounds and the winner has the highest total of all rounds. I am trying to write a query that adds up the round scores for each player for each game and returns the highest score and player id of each game. It sounds so easy but I'm struggling to do the grouping inside a subqueary to get only the max score for each game.

Here is a simplified version of the table

Code:

+----+------+--------+-------+-------+
| id | game | player | round | score |
+----+------+--------+-------+-------+
|  1 |    1 |      1 |     1 |     3 |
|  2 |    1 |      2 |     1 |     3 |
|  3 |    1 |      1 |     2 |     1 |
|  4 |    1 |      2 |     2 |     2 |
|  5 |    1 |      3 |     1 |     5 |
|  6 |    1 |      3 |     2 |     1 |
|  7 |    2 |      1 |     1 |     8 |
|  8 |    2 |      2 |     1 |     4 |
|  9 |    2 |      3 |     1 |     1 |
| 10 |    2 |      1 |     2 |     1 |
| 11 |    2 |      2 |     2 |     4 |
| 12 |    2 |      3 |     2 |     6 |
+----+------+--------+-------+-------+

If I run
select game, player, sum(score) from scorecard group by game, player
I get

Code:

+------+--------+------------+
| game | player | sum(score) |
+------+--------+------------+
|    1 |      1 |          4 |
|    1 |      2 |          5 |
|    1 |      3 |          6 |
|    2 |      1 |          9 |
|    2 |      2 |          8 |
|    2 |      3 |          7 |
+------+--------+------------+

And now all I need to do is limit the result to the higest scores in each game which I think I need to do by using a sub-query and max but I always get the wrong answer.

Can anybody point out what I'm missing or how I could re-structure the table to make this work.

Many thanks if you can help at all.

Robin

mike_bike_kite · #2 (**permalink**) 05-08-09, 12:19

Quote:

Originally Posted by railes

And now all I need to do is limit the result to the higest scores in each game which I think I need to do by using a sub-query and max but I always get the wrong answer.

Can anybody point out what I'm missing or how I could re-structure the table to make this work.

Start by using order by to get the top scores first. Then use limit to just show a certain number of them. There's no need for sub-queries.

Mike

railes · #3 (**permalink**) 05-08-09, 12:25

Hi Mike,

Thanks for the suggestion. I have tried messing about with limit and order but order could give me two scores from the same game. In the test data I supplied I have simplified the table a lot to try and make it easier to understand and I included that possibility.

If I try
select game, player, sum(score) from scorecard group by game, player order by sum(score) desc limit 2
I get

Code:

+------+--------+------------+
| game | player | sum(score) |
+------+--------+------------+
|    2 |      1 |          9 |
|    2 |      2 |          8 |
+------+--------+------------+

Had you another idea on how to do the order or limit?

Robin

mike_bike_kite · #4 (**permalink**) 05-08-09, 12:31

Two scores from the same game? I don't understand sorry - can you give a better example. If it's including two games with the same score then that's a different matter.

mike_bike_kite · #5 (**permalink**) 05-08-09, 12:51

You will need a sub query then. Use the same code but check that a record doesn't exists for the same game with a higher score.

You can make your data (much) easier to read by highlighting your tables and then using the # button to preserve the spacing etc in your data.

The SQL is more difficult than you imagined because the table design isn't optimal (I believe).

Mike

railes · #6 (**permalink**) 05-08-09, 14:53

Thanks Mike for top tip on # for tables. I've redone them to help others understand more clearly.

On optimal table design, I have been trying to think of a different way of designing it to make it easier to get the results but everything I've tried so far has just given me repitition of date. Any thoughts?

mike_bike_kite · #7 (**permalink**) 05-08-09, 15:26

Few questions before I commit myself. What's the game you're playing? It might help just to picture what's going on. Are there always 2 players in a game. Do people ever get by's? Is it always a knockout tournament or do you need to cover round robin or swiss style tournaments. I'm hoping to be playing in a round robin backgammon tournament next week!

railes · #8 (**permalink**) 05-08-09, 16:25

It's a version of the card game whist. 4 players, it is normally played over 5 or 10 rounds (one for each suit plus 'no trumps') and on each round a player may score between 0 and 23 points. Winner is highest combined score.

We play with friends reguarly and we like to record the numbers. We probably have about 50+ games recorded and I thought it would be fun and easy to enter them into at database and pull out stats like who had won most games, who came 2nd most often, most successful on each suit etc. Thinking about it now, I could record the finishing positions (ie 1 to 4) against a game id in a seperate table but since it can be derived from the numbers it seems like wasted effort.

I'm sure the answer is a query of the query above, but it's just out of my reach.

mike_bike_kite · #9 (**permalink**) 05-08-09, 16:57

It might be out of my reach too but this is my 1st guess :

Code:

select	t1.game, t1.player, t1.tot_score
from	(       -- total score for each player for each game
		select	game, player, sum( score ) tot_score
		from	scorecard
		group by game, player
	) t1,
	(       -- highest score in each game
		select	game, max( tot_score ) max_score
		from	(       -- total score for each player for each game
				select	game, player, sum( score ) tot_score
				from	scorecard
				group by game, player
			) t2
		group by game
	) t3
where	t1.game = t3.game
	and t1.tot_score = t3.max_score

I haven't tried the code so there's probably a few little errors in there. If 2 players have the same total score (is this possible?) then both names are shown.

railes · #10 (**permalink**) 05-08-09, 19:24

It's a very good first guess. That last group statement is fantastic and the bit that I keep missing. I'll plug it into some proper data and see what it looks like. I'll let you know. Very clever and very quick.

Internet Services

Web Development

Digital Marketing

Consumer Tech