How can I add a column that increments on another column in same table?

My best advice to you is, Don't do this. Storing information that can be derived from other information in the database is generally considered very poor design, and attempting to rely on the order of the rows in the database is a sure path to madness.

Here's a first pass at normalizing your table:

-- Table: teams

-- DROP TABLE teams;

CREATE TABLE teams
(
  team_id character(3) primary key,
  team_name varchar(255),
  team_city varchar(255)
) engine=innodb;

-- Table: starting_pitchers_game_log

-- DROP TABLE starting_pitchers_game_log;

CREATE TABLE starting_pitchers_game_log
(
  pitcher_id character(10) NOT NULL,
  game_date date NOT NULL,
  opposing_team character(3),
  game_seq integer NOT NULL,
  outcome character(1),
  innings_pitched real,
  bfp integer,
  hits integer,
  runs integer,
  errors integer,
  homeruns integer,
  bb integer,
  k integer,
  ibb integer,
  hbp integer,
  wp integer,
  balks integer,
  CONSTRAINT starting_pitcher_log_pk
      PRIMARY KEY (pitcher_id , game_date , game_seq ),
  CONSTRAINT team_fk FOREIGN KEY (opposing_team)
      REFERENCES teams (team_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
) engine=innodb;

(I don't follow baseball, so I could only guess at some of the column names.) Note that the year_id, month_id and day_id columns are gone, as those values can be recreated from the game_date column as I indicated in the comments. Also your game_id column is gone; this can be recreated from concatenating opposing_team, game_date and game_seq (which I presume is to account for double-headers &c.) I've also converted W and L into a single column intended to hold the values "W" (win), "L" (loss), and "T" (tie).

The teams table provides a lookup table for the 3-char team ids. It can be expanded to hold any other team data you want. (Note it is meant to describe the team itself; team activities would go in another table.)

To answer your question about the "constraint" clauses, the first one (CONSTRAINT starting_pitcher_log_pk and the indented line below it) specifies that the concatenation of those three columns serves as the primary unique identifier for each row in the table. The second one (CONSTRAINT team_fk FOREIGN KEY (opposing_team) and the indented lines below it) means that for a value to be placed in the opposing_team column it must already exist in the teams.team_id column; you can't play against a team that doesn't exist.

Now to work on actually answering your original question. The best solution I could come up with on MySQL was a scratch table and a stored procedure, as follows:

-- Table: ip_subtotal

-- DROP TABLE ip_subtotal;

CREATE TABLE ip_subtotal
(
  pitcher_id char(10) NOT NULL,
  game_date date NOT NULL,
  game_seq int(11) NOT NULL,
  innings_pitched double,
  ip_total double DEFAULT '0.0',
  CONSTRAINT ip_subtotal_pk
      PRIMARY KEY (pitcher_id , game_date , game_seq )
) ENGINE=InnoDB;

And the stored procedure:

------------------------------------------------------------------------------    --
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE PROCEDURE accumulate_innings()
BEGIN
    DECLARE pit_id CHAR(10);
    DECLARE gdate DATE;
    DECLARE seq INT;
    DECLARE in_pit REAL;
    DECLARE accum REAL;
    DECLARE prev_year YEAR(4);
    DECLARE end_of_cursor BOOLEAN;

    DECLARE c1 CURSOR FOR
        SELECT pitcher_id, game_date, game_seq, innings_pitched
            FROM ip_subtotal
            ORDER BY pitcher_id, game_date, game_seq;

    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET end_of_cursor := TRUE;

    TRUNCATE TABLE ip_subtotal;
    INSERT INTO ip_subtotal
        SELECT pitcher_id, game_date, game_seq, innings_pitched, 0.0
            FROM starting_pitchers_game_log;

    SET prev_year := 0;
    OPEN c1;

    fetch_loop: LOOP
        FETCH c1 INTO pit_id, gdate, seq, in_pit;
        IF end_of_cursor THEN
            LEAVE fetch_loop;
        END IF;
        IF YEAR(gdate) != prev_year THEN
            SET accum := 0.0;
            SET prev_year := YEAR(gdate);
        END IF;
        SET accum := accum + in_pit;
        UPDATE ip_subtotal
            SET ip_total = accum
            WHERE pitcher_id = pit_id
              AND game_date = gdate
              AND game_seq = seq;
    END LOOP;
    CLOSE c1;
END

This procedure clears out the table ip_subtotal, populates it from the main table, then rolls up the running total for innings pitched. It also uses a simple control-break to reset the accumulator at the start of the year. After you've run the procedure by executing

CALL accumulate_innings();

you can query the ip_subtotal table or join it back to the starting_pitchers_game_log table as desired.

The procedure could also be extended to accept a start and end date; I leave that as an exercise for the reader.

Hope this helps; it was interesting and forced me to learn a little MySQL.