Finding data in rows and data in combined rows

[cliftonbazaar]

I know the subject is confusing but it's the best I can do 

 

If my Data is (small sample)

NameplayerIDmatchIDInningsScore

James1115

James1128

Darren2113

Darren2129

(Please note that name and playerID are always the same and are only displayed here for example purposes)

and my sql is

$query = "SELECT playerID, name,
		SUM(runs) AS runs_scored,
		MAX(runs) AS highest_score
	FROM matchPlayer GROUP BY playerID"; 

Will display the results of

James has scored 13 runs with a highest score of 8

Darren has scored 12 runs with a highest score of 9

 

What i am now trying to achieve is to also get the highest combined score in one match (combining the runs from innings 1 and 2 from the same matchID) and I have no idea where to start  :'(

[Muddy_Funster]

You're doing that already...arn't you? your total runs is telling you what the combined score over both innings is. 

[cliftonbazaar]

You're doing that already...arn't you? your total runs is telling you what the combined score over both innings is. 

Note that it is only a small sample, the actual database has 100's of matches in it, each match has two innings.

[Muddy_Funster]

I'm not getting it, what do you want to do that you are not already doing?

[cliftonbazaar]

If the data is

NameplayerIDmatchIDInningsRuns

James1118

James1122

James1216

James12112

I want to find the total runs for the player - 28 (I can do), their highest amount of runs in any innings - 12 (I can do), but what I can't do is find the highest amount of runs scored in a match (match 1 had 10 runs while match 2 had 18 runs, so 18 is the answer).

Can I find the answer in the one query or do I need to use another query?

 

Another question - if I find the Max value how would I find the rest of the row it was in, or do I need another query for this?

[fenway]

You'll need to wrap then in yet another select.