Aggregate Query...
-
I have the following tables: CREATE PLAYER ( PLAYERNO INT UNIQUE NOT NULL, NAME CHAR(20), ... PRIMARY KEY(PLAYERNO) ) CREATE TABLE GAME ( GAMENO INT UNIQUE NOT NULL, PLAYER1 INT, PLAYER2 INT, HOURS INT, ... PRIMARY KEY(GAMENO), FOREIGN KEY(PLAYER1) REFERENCES PLAYER(PLAYERNO), FOREIGN KEY(PLAYER2) REFERENCES PLAYER(PLAYERNO) ) How do I retrieve, for every player, the total number of hours he played as Player1 and the total number of hours played as Player2? The resulting table should be something like: PLAYERNO HRS-PLAYED-AS-P1 HRS-PLAYED-AS-P2
-
I have the following tables: CREATE PLAYER ( PLAYERNO INT UNIQUE NOT NULL, NAME CHAR(20), ... PRIMARY KEY(PLAYERNO) ) CREATE TABLE GAME ( GAMENO INT UNIQUE NOT NULL, PLAYER1 INT, PLAYER2 INT, HOURS INT, ... PRIMARY KEY(GAMENO), FOREIGN KEY(PLAYER1) REFERENCES PLAYER(PLAYERNO), FOREIGN KEY(PLAYER2) REFERENCES PLAYER(PLAYERNO) ) How do I retrieve, for every player, the total number of hours he played as Player1 and the total number of hours played as Player2? The resulting table should be something like: PLAYERNO HRS-PLAYED-AS-P1 HRS-PLAYED-AS-P2
I guess this will work:
select 'PLAYERNO' + game.player1 ,sum(game.hours) + 'hours played' as P1,'PLAYERNO' + game.player2 ,sum(game.hours)+'hours played' as P2 from game where gameno = @yournumber
I'm not sure but maybe you nedd some data conversion when you add
'PLAYERNO'togame.playerMazy **"If I go crazy then will you still Call me Superman If I’m alive and well, will you be There holding my hand I’ll keep you by my side with My superhuman might Kryptonite"**Kryptonite-3 Doors Down