Topic: Using subqueries and expressions

[msaz87]

Hey all,

I'm not too familiar with more complicated queries, so I'll try to explain what I'm trying to do here:

I have a DB table that organizes stats for a league. Each player id shows up multiple times with the multiple weeks of the league, so to show a summary of stats I do the following:

$stats_query = "SELECT 
SUM(td_pass), 
SUM(int_pass), 
SUM(td), 
SUM(sacks), 
SUM(int_d),
SUM(`int`) AS exp 
FROM xxx 
WHERE player_id = '$player_id'";

In addition to summarizing these, there's a "MVP Points" calculation using a formula based on the stats:


if($week == "-1") {

$start_mvp = 20;

} else {

$start_mvp = 0;

}

$tdpass_mvp = 4;
$intpass_mvp = 3;
$td_mvp = 6;
$exp_mvp = 2;
$sacks_mvp = 2;
$intd_mvp = 3;

$mvp_points = $start_mvp + ($tdpass_mvp * $row['SUM(td_pass)']) - ($intpass_mvp * $row['SUM(int_pass)']) + ($td_mvp * $row['SUM(td)']) + ($exp_mvp * $row['exp']) + ($sacks_mvp * $row['SUM(sacks)']) + ($intd_mvp * $row['SUM(int_d)']);

echo $mvp_points;


As you can see above, the MVP Points are derived from this formula and there's not actually a column in the DB table for them... which is where my issue is:

I'm looking to query the leader in MVP points either for a specific week or cumulative. I'm not sure how you would utilize subqueries or expressions to do this, or if there is just another way to do it altogether?

Hopefully this makes sense..

Thanks in advance for any help!

[fenway]

You'd have to run another query on this output as a dervied table to figure it out -- and I'd use column aliases in general.

[msaz87]

You'd have to run another query on this output as a dervied table to figure it out -- and I'd use column aliases in general.

So if I run a query as such:



$stats_query

= "

SELECT 

SUM(td_pass), 

SUM(int_pass), 

SUM(td), 

SUM(sacks), 

SUM(int_d),

SUM(`int`) AS exp 

FROM xx 

WHERE player_id 

= '$player_id'";

$stats_results

= mysql_query($stats_query) or die(mysql_error());

while($row = mysql_fetch_array($stats_results)) { 

$mvp_points = $start_mvp + ($tdpass_mvp * $row['SUM(td_pass)']) - ($intpass_mvp * $row['SUM(int_pass)']) + ($td_mvp * $row['SUM(td)']) + ($exp_mvp * $row['exp']) + ($sacks_mvp * $row['SUM(sacks)']) + ($intd_mvp * $row['SUM(int_d)']);

}



How would I be able to run through this query and maintain the greatest result for $mvp_points? Note: In the above query, that's already within another query for each player_id in a league.

Thanks for the help!

[fenway]

I can't follow that.

[msaz87]

Sorry, let me try to simplify it: is it possible to run a query that will output numeric results in a loop and when the loop is finished, something finds the highest value that was in the loop and displays it. So if the query looped out 4, 6, 2, 20, 5, 12 it would do so unseen and at the end come back with the result of 20. the db numbers would be put into an equation during the loop, giving those final figures to check. Does that make any more sense? Thanks for the help!


I

[fenway]

Yes... and I believe I alluded to this earlier.  Simply run this query as a derived table, and use MAX().

[msaz87]

Yes... and I believe I alluded to this earlier.  Simply run this query as a derived table, and use MAX().

I apologize if I'm going in circles and you already explained this... but you're saying that in the query I can essentially do the expressions and use aliases to create a column that isn't actually in the table?

Like I said, I have columns basically like A, B, C and D and query the sums of each, but then to formulate the number I want, each column is multiplied by a number then they're either added or subtracted to eachother...

So I would be able to SUM columns A, B, C and D, doing the formula to each within the query, use an alias to return the whole sum AS whatever and then query the MAX of that alias?

[msaz87]

So I was able to get as far as calculating the numbers I want to find the max of within the query, it's just finding the max I'm not sure of now:

The below calculates the number, "mvp_points"


$math_query

= "

SELECT 

(SUM(td_pass) * $tdpass_mvp) - 

(SUM(int_pass) * $intpass_mvp) + 

(SUM(td) * $td_mvp) + 

(SUM(sacks) * $exp_mvp) + 

(SUM(`int`) * $sacks_mvp) + 

(SUM(int_d) * $intd_mvp) AS mvp_points 

FROM xxx 

WHERE player_id 

= '$player_id'"; 

$math_results

= mysql_query($math_query) or die(mysql_error());

while($row = mysql_fetch_array($math_results)) { 

echo $row['mvp_points'];

}


So the only thing I'm looking to do now is add a MAX() around the entire thing for "mvp_points", but with the derived table, I'm uncertain how to achieve this.

Any help is greatly appreciated... thanks so much!