Printable Version of Topic

Posted by: asmith Nov 9 2008, 08:37 AM

Hi,

Is there a way that I can group by more than 1 field?

For example I have this :

field1 field2 field3
john tom jack
alex john alice
alex jack john

I group by "john". a name can only be in one of the field in the same time. no duplicate name in the same row.

Is it possible?

Posted by: Brian Chandler Nov 9 2008, 09:50 AM

http://www.google.com/search?client=opera&rls=en&q=mysql+%22group+by%22+%22more+than+one+field%22&sourceid=opera&ie=utf-8&oe=utf-8

Does (e.g.) the first hit answer your question?

Incidentally, why do you have three fields with apparently the same function? What _are_ field1, field2, field3? I mean, what do they _mean_?

Posted by: asmith Nov 9 2008, 03:52 PM

They are players in a game.

fields are :

player1 player2 player3 player4 points


so in a game (a row that will be inserted there)
for example "john" can be recorded there as player1 , and in some other game , he can be inserted in the player2 field ....

when I want to have john stat, so that I could use group by and count all his points in all rows...

Any idea?

Another way is,
I remove the fields, player1 , player2 ... and just use "player" field, and I set an game_id field , and for example insert 4 players, by 4 rows with the same game ID.
the problem with that is, I can't get tabular data from all my data from table by some pagination limit.

for example if the first page is showing 30 rows, and row 28-29-30 and 31 are 1 game (4 players). the row 31 would go to the 2nd page and first page result will be mess up.

How to solve this ?

maybe to sum up :
there are rows of games, each row contain points for those players, I need to have players points (sum all points in all rows that he is in the player field).
and I need to have all rows. as maybe "history of games" or something.

Posted by: Brian Chandler Nov 9 2008, 11:44 PM

Well you can simply retrieve all the records including 'John' (player1='John' OR player2= ...), and add up his points.

There are two approaches to using SQL: if you are an SQL guru you probably want to write everything as a Very Clever SQL query, but if you're not (I'm not), you just use the database as a way to store stuff, and write programs to Do Things like finding totals and whatever in the way some of us have been doing for decades.



Not entirely sure what you mean by "pagination limit", but it isn't how to do things.

If there are always four players, and you might want to know which John was (W, N, E, or S, for example), then it makes sense to have a table with four fields. If the number of players is variable, and all there is associated with each one is a score, then it makes sense to have a table with a field for the game ID, and a field for "Player". When you print out the players in each game, you do the formatting in the printing program ("print" meaning generate a web page, usually).



Easy: stop thinking of the database as a picture of what is going to be on the output page.



You are generating web pages, right? Do you have current versions you can show us? What programming language are you using?

Posted by: Remote DBA Jan 23 2009, 08:08 AM

Hey asmith!
How about normalization of your DB?
I mean another table Players (ID, PlayerName, PlayerNumber, GameID)