MySQL Forums
Forum List  »  General

Re: GROUP BY (nested group by required?) help
Posted by: Mike Cossaboom
Date: June 24, 2013 05:53PM

My apologies, (and thanks for taking a look btw,) my code is a little rough around the edges, I didn't dare post it. Here's a clean version.

SELECT Name, Year, TM, sum(GamesStarted) AS GS
FROM gamestats
WHERE Name='Montana'
GROUP BY Year DESC
WITH ROLLUP;

*******************************************************************

CREATE TABLE 'gamestats' (
'ID' int(10) NOT NULL,
'TM' char(3) NOT NULL,
'Name' varchar(45) NOT NULL,
'Year' smallint(5) NOT NULL,
'Week' tinyint(3) NOT NULL,
'GamesStarted' tinyint(1) NOT NULL,
PRIMARY KEY ('ID')
)

INSERT INTO gamestats(ID, TM, Name, Year, Week, GamesStarted)
VALUES('3413', 'SF', 'Montana', '1991', '1', '1')
VALUES('3413', 'SF', 'Montana', '1991', '2', '1')
VALUES('3413', 'SF', 'Montana', '1991', '3', '1')
VALUES('3413', 'SF', 'Montana', '1991', '4', '1')
VALUES('3413', 'SF', 'Montana', '1992', '1', '1')
VALUES('3413', 'SF', 'Montana', '1992', '2', '1')
VALUES('3413', 'SF', 'Montana', '1992', '3', '0')
VALUES('3413', 'SF', 'Montana', '1992', '4', '0')
VALUES('3413', 'KC', 'Montana', '1993', '1', '0')
VALUES('3413', 'KC', 'Montana', '1993', '2', '0')
VALUES('3413', 'KC', 'Montana', '1993', '3', '1')
VALUES('3413', 'KC', 'Montana', '1993', '4', '1')
VALUES('3413', 'KC', 'Montana', '1994', '1', '1')
VALUES('3413', 'KC', 'Montana', '1994', '2', '1')
VALUES('3413', 'KC', 'Montana', '1994', '3', '0')
VALUES('3413', 'KC', 'Montana', '1994', '4', '1')

************************************************************************

NOTE: A player's "stat-line" is entered for each game. The db above is a 4 year, 16 game sample that identifies the player ID, his name, the team he plays for, the year and week of each game played and whether he started or not.

This sample data, as coded, will display

NAME | YEAR | TM | GS
MONTANA | 1994 | SF | 3
MONTANA | 1993 | SF | 2
MONTANA | 1992 | SF | 2
MONTANA | 1991 | SF | 4

I would like it to read...

NAME | YEAR | TM | GS
MONTANA | 1994 | KC | 3
MONTANA | 1993 | KC | 2
MONTANA | 1992 | SF | 2
MONTANA | 1991 | SF | 4

I'm not sure how to note that "KC" was his team for the last two seasons.



Edited 1 time(s). Last edit at 06/24/2013 05:54PM by Mike Cossaboom.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.