The Web Design Group

... Making the Web accessible to all.

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> groupby multiple fields
asmith
post Nov 9 2008, 08:37 AM
Post #1


Advanced Member
****

Group: Members
Posts: 198
Joined: 26-December 07
Member No.: 4,586



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?
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Brian Chandler
post Nov 9 2008, 09:50 AM
Post #2


Jocular coder
********

Group: Members
Posts: 2,460
Joined: 31-August 06
Member No.: 43



http://www.google.com/search?client=opera&...-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_?
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
asmith
post Nov 9 2008, 03:52 PM
Post #3


Advanced Member
****

Group: Members
Posts: 198
Joined: 26-December 07
Member No.: 4,586



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.

User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Brian Chandler
post Nov 9 2008, 11:44 PM
Post #4


Jocular coder
********

Group: Members
Posts: 2,460
Joined: 31-August 06
Member No.: 43



QUOTE(asmith @ Nov 10 2008, 05:52 AM) *

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?


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.

QUOTE


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.



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).

QUOTE


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 ?


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

QUOTE


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.


You are generating web pages, right? Do you have current versions you can show us? What programming language are you using?
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Remote DBA
post Jan 23 2009, 08:08 AM
Post #5





Group: Members
Posts: 8
Joined: 23-January 09
Member No.: 7,617



Hey asmith!
How about normalization of your DB?
I mean another table Players (ID, PlayerName, PlayerNumber, GameID)
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 



- Lo-Fi Version Time is now: 28th March 2024 - 03:52 AM