MySQL Query loop HELP without using PHP

Question

I have the following query:

SET @year1 = 2011;

select  B.YearID, sum(B.ab), sum(B.h), sum(B.h)/ sum(B.ab)
    From Batting B join Pitching P 
    on (P.playerID = B.playerID and B.yearID = P.yearid and B.ab > 0 ) 

        where  B.yearid = @year1

This works fine to retrieve results for one year. But can I do a loop using on mySQL that would allow me to execute the query and see results for each year?

This code does not work and produces syntax errors:

SET @year1 = 2011;

While @year1 <= 2014 DO

SET @year1 = @year1 + 1;

select  B.YearID, sum(B.ab), sum(B.h), sum(B.h)/ sum(B.ab)
    From Batting B join Pitching P 
    on (P.playerID = B.playerID and B.yearID = P.yearid and B.ab > 0 ) 

        where  B.yearid = @year1;
End While

Can I write a query in MySQL or must I use a language such as PHP?

Norbert · Accepted Answer · 2015-07-13 21:32:47Z

1

Simplest it to use group by:

select  B.YearID, sum(B.ab), sum(B.h), sum(B.h)/ sum(B.ab)
From Batting B join Pitching P 
on (P.playerID = B.playerID and B.yearID = P.yearid and B.ab > 0 ) 
where b.yearid>=2011 and b.yearid<=2014
group by B.yearid;

This will give you the list at once.

A loop would also be possible in a stored procedure however you would have to use a WHILE for syntax reasons:

CREATE PROCEDURE someProcedure(IN start_year INT, end_year INT) 
BEGIN
  DECLARE v_year_id INT;
  SET v_year_id=start_year;
  WHILE(v_year_id<end_year)
  LOOP
    select  B.YearID, sum(B.ab), sum(B.h), sum(B.h)/ sum(B.ab)
    From Batting B join Pitching P 
    on (P.playerID = B.playerID and B.yearID = P.yearid and B.ab > 0 ) 
    WHERE yearId=v_year_id;
    group by B.yearid; 
    SET v_year_id=v_year_id+1;
  END WHILE;
END;

The local variable v_year_id could also be eliminated but has been added here for clarity. If you eliminate v_year_id it would look like:

CREATE PROCEDURE someProcedure(IN start_year INT, end_year INT) 
BEGIN
  WHILE(start_year<end_year)
  LOOP
    select  B.YearID, sum(B.ab), sum(B.h), sum(B.h)/ sum(B.ab)
    From Batting B join Pitching P 
    on (P.playerID = B.playerID and B.yearID = P.yearid and B.ab > 0 ) 
    WHERE yearId=start_year;
    group by B.yearid; 
    SET start_year=start_year+1;
  END WHILE;
END;

Calling the procedure would be done with:

CALL someProcedure(2011,2014);

edited Jul 13, 2015 at 21:32

answered Jul 13, 2015 at 16:09

Norbert

6,1043 gold badges19 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DVDmmike Over a year ago

That does exactly what I need, thank you. But I'd still like to know if I am able to use some sort of loop?

Norbert Over a year ago

Now with loop (p.s. appreciation on stackoverflow is shown by marking the answer as OK with the mark and upvoting the answer).

Collectives™ on Stack Overflow

MySQL Query loop HELP without using PHP

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related