How do I insert a working case statement into other code?

See more:

MySQL

I have this working case statement which gives me a value.
Trying to add select statements before to give information on resulting value but get error or get 2 separate data rows?

How can I incorporate this case statement into my other code?

Sure it must be simple but just can't get it to work?

Thought that I may need to place this into variable but got errors on that too?

I am using pre 2012 sql database

Sample Data:

Team	        Bowler	        Gm1	Gm2	Gm3	Ttl	Max Score
FOUL 5 MINUS 1	RICK CAMPBELL	234	314	226	774	314
FULL HOUSE	   JEANNE GIRARD	243	249	239	731	249
4 SURE	           JIM SILVER	195	208	302	705	302
3 SMART ASSES	  LARRY CROSS	199	231	276	706	276

OUTPUT WITHOUT NEW CASE:

Date	Wk	Tm	Team	Bowler	WK TOTAL	GRAND TTL	GMS	AVG	HSS	HDCP
12-09-16	1	5	THE EAGLES	ERIC RUSSCHER	369	369	3	123	369	102
12-09-16	1	5	THE EAGLES	LINDA RUSSCHER	492	492	3	164	492	69
12-09-16	1	5	THE EAGLES	BILL MOORE	    582	582	3	194	582	45
12-09-16	1	5	THE EAGLES	DONNA HEDLEY	581	581	3	194	581	45
19-09-16	2	5	THE EAGLES	ERIC RUSSCHER	417	786	6	131	417	95
19-09-16	2	5	THE EAGLES	LINDA RUSSCHER	501	993	6	166	501	68

What I have tried:

Here is the code i am trying to add "max Score" for each bowler as i have in the case statement below. Both work separately but not together.

( SELECT "Date","Wk","Tm","Team","Bowler","WEEK'S TOTAL","GRAND TTL", 
 "GMS", "AVG","HSS","HGS GM1","HGS GM2","HGS GM3", "MaxGm","HDCP") 
UNION 
( SELECT Date,Week,TeamNum,Team,Bowler, 
 /* TTL */ 
 FORMAT ((Gm1+Gm2+Gm3),0) as "TOTAL" , 
 /* GRAND */ 
 FORMAT ((SELECT SUM(Gm1+Gm2+Gm3) 
          FROM b_Scores L WHERE L.Bowler = R.Bowler AND L.Date <= R.Date),0), 
 /* GAME COUNT */ 
 FORMAT ((SELECT COUNT(Gm1)*3 
          FROM b_Scores L WHERE L.Bowler = R.Bowler AND L.Date <= R.Date),0), 
 /* RUNNING AVG */ 
 FORMAT ((SELECT SUM(Gm1+Gm2+Gm3) 
          FROM b_Scores L WHERE L.Bowler = R.Bowler AND L.Date <= R.Date) 
         / (SELECT COUNT(Gm1)*3 FROM b_Scores L 
            WHERE L.Bowler = R.Bowler AND L.Date <= R.Date),0) as "RAvg" , 
 /* HIGH GM SINGLE - FUTURE */ /* Max value for 3 Game Scratch */ 
 FORMAT ((SELECT MAX(Gm1+Gm2+Gm3) 
          FROM b_Scores L WHERE L.Bowler = R.Bowler 
          AND L.Date <= R.Date),0) as "Max Scratch", 

/* MAX GAME TO DATE 



 /* ADDING RUNNING HANDICAP. (250-RAVG) *0.8 */ 
 FORMAT (((250 -( SELECT SUM(Gm1+Gm2+Gm3) 
          FROM b_Scores L WHERE L.Bowler = R.Bowler AND L.Date <= R.Date) / ( SELECT COUNT(Gm1)*3 
          FROM b_Scores L WHERE L.Bowler = R.Bowler AND L.Date <= R.Date ))*0.8),0) 
 FROM b_Scores R 
 JOIN b_Matches ON Week = MatchWk AND TeamNum = MatchTm 
)

SELECT MAX(Score)
FROM  
( SELECT            
         CASE WHEN Gm1 >= Gm2
               AND Gm1 >= Gm3 THEN Gm1
               WHEN Gm2 >= Gm1
               AND Gm2 >= Gm3 THEN Gm2
               WHEN Gm3 >= Gm1
               AND Gm3 >= Gm2 THEN Gm3
               ELSE Gm1
          END AS Score          
 FROM b_Scores	
 WHERE TeamNum =4
) AS score_query ;

As Requested: Exact Code put together with error.
Tested in Mysql database (pre2012)

( SELECT "Date","Wk","Tm","Team","Bowler","WEEK'S TOTAL","GRAND TTL", 
 "GMS", "AVG","HSS", "MaxGm","HDCP") 
UNION 
( SELECT Date,Week,TeamNum,Team,Bowler, 
 /* TTL */ 
 FORMAT ((Gm1+Gm2+Gm3),0) as "TOTAL" , 
 /* GRAND */ 
 FORMAT ((SELECT SUM(Gm1+Gm2+Gm3) 
          FROM b_Scores L WHERE L.Bowler = R.Bowler AND L.Date <= R.Date),0), 
 /* GAME COUNT */ 
 FORMAT ((SELECT COUNT(Gm1)*3 
          FROM b_Scores L WHERE L.Bowler = R.Bowler AND L.Date <= R.Date),0), 
 /* RUNNING AVG */ 
 FORMAT ((SELECT SUM(Gm1+Gm2+Gm3) 
          FROM b_Scores L WHERE L.Bowler = R.Bowler AND L.Date <= R.Date) 
         / (SELECT COUNT(Gm1)*3 FROM b_Scores L 
            WHERE L.Bowler = R.Bowler AND L.Date <= R.Date),0) as "RAvg" , 

 /* HIGH GM SINGLE - FUTURE */ /* Max value for 3 Game Scratch */ 
 FORMAT ((SELECT MAX(Gm1+Gm2+Gm3) 
          FROM b_Scores L WHERE L.Bowler = R.Bowler 
          AND L.Date <= R.Date),0) as "Max Scratch", 

/* MAX GAME TO DATE per player  */

/* (Line #26) */
SELECT MAX(Score)
FROM  
( SELECT            
         CASE WHEN Gm1 >= Gm2
               AND Gm1 >= Gm3 THEN Gm1
               WHEN Gm2 >= Gm1
               AND Gm2 >= Gm3 THEN Gm2
               WHEN Gm3 >= Gm1
               AND Gm3 >= Gm2 THEN Gm3
               ELSE Gm1
          END AS Score          
 FROM b_Scores	
 ORDER by TeamNum, Bowler
 
) AS score_query ;


 /* ADDING RUNNING HANDICAP. (250-RAVG) *0.8 */ 
 FORMAT (((250 -( SELECT SUM(Gm1+Gm2+Gm3) 
          FROM b_Scores L WHERE L.Bowler = R.Bowler AND L.Date <= R.Date) / ( SELECT COUNT(Gm1)*3 
          FROM b_Scores L WHERE L.Bowler = R.Bowler AND L.Date <= R.Date ))*0.8),0) 
 
 FROM b_Scores R 
 JOIN b_Matches ON Week = MatchWk AND TeamNum = MatchTm 
)

Error Msg:

 MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT MAX(Score)
FROM  
( SELECT            
         CASE WHEN Gm1 >= Gm2
' at line 26