Jump to content

Finding data in rows and data in combined rows


cliftonbazaar

Recommended Posts

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

 

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  :'(

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

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

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?

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.