Help with query

[86Stang]

I recently requested help in the PHP Help forums in thread http://www.phpfreaks.com/forums/index.php/topic,213038 asking for help.  akitchin was a great supporter and he asked that I come to this neck of the woods and see if someone could help me nail this down before I have to tell the customer that it's not possible.

 

Towards the end of the thread he was having me attempt a CASE command inside the query and it resulted in an 'unknown column' error and my hope is that a MySQL guru can see what's going on.

 

Thanks!

[revraz]

Unknown Column usually is one of two things.  The fieldname you are referencing does not exist, or you are not using single quotes around a variable.

[akitchin]

Unknown Column usually is one of two things.  The fieldname you are referencing does not exist, or you are not using single quotes around a variable.

 

the reason it's unknown here is because it's an aggregate function alias - it won't be calculated until after the WHERE clause is performed.

[akitchin]

just a thought - perhaps you simply need to not use the alias and insert the calculation as well:

 

{query sql="SELECT
`team`,
SUM(IF(`result`='Win',1,0)) AS `wins`,
SUM(IF(`result`='Loss',1,0)) AS `losses`,
        CASE WHEN SUM(IF(`result`='Win',1,0)) = 0 AND SUM(IF(`result`='Loss',1,0)) = 0 THEN 0.500 ELSE ROUND(SUM(IF(`result`='Win',1,0)) / (SUM(IF(`result`='Win',1,0)) + SUM(IF(`result`='Loss',1,0))), 2) END AS `percentage`
FROM
(
	SELECT
		`home_team` AS `team`,
		`home_results` AS `result`,
	FROM
		`database`

	UNION ALL

	SELECT
  		`away_team` AS `team`,
		`away_results` AS `result`,
	FROM
		`database`

  ) AS `t`
GROUP BY
	`team`
"}

 

even if this does work, it's hella-ugly.  i'm hoping a MySQL guru (*ahem* FENWAY) can come along and clean it up.

[akitchin]

ANOTHER thought - you could simply store the result as a 1 or a 0 for a win or loss respectively.  this would greatly simplify your calculations and make the storage of the results more efficient.

[fenway]

Towards the end of the thread he was having me attempt a CASE command inside the query and it resulted in an 'unknown column' error and my hope is that a MySQL guru can see what's going on.

Could you post the error message?

[akitchin]

"Unknown column 'wins' in 'field list'"

 

(from the other thread)

 

the previous query i offered was the same as the one i posted above, except using the SUM() aliases in the CASE statement.

[Hooker]

First off, i'd take a completely un-tested, off the cuff guess and say this should solve the original questions php problem:

<?
// if no games played yet, give the team a .500 percentage
if ($Wins == 0)&&($Losses == 0)
{
    $Perc = .500;
}
// if any wins AND no losses, give the team a 1.00 percentage
elseif ($Wins > 0)&&($Losses == 0)
{
    $Perc = 1.00;
}
// if any losses AND no wins, give the team a 0.00 percentage
elseif ($Wins == 0)&&($Losses > 0)
{
    $Perc = 0.00;
}
// otherwise figure the win to loss percentage
else 
{
    $Perc = $Wins / ($Wins + $Losses);
}

// show results
echo $Perc;

?>

 

as for the sql, CASE statements are only available in stored routines, also, this should fix the unknown column error:

	SUM(IF(`result`='Win',1,0)) AS wins,
SUM(IF(`result`='Loss',1,0)) AS losses,

[akitchin]

First off, i'd take a completely un-tested, off the cuff guess and say this should solve the original questions php problem:

<?
// if no games played yet, give the team a .500 percentage
if ($Wins == 0)&&($Losses == 0)
{
    $Perc = .500;
}
// if any wins AND no losses, give the team a 1.00 percentage
elseif ($Wins > 0)&&($Losses == 0)
{
    $Perc = 1.00;
}
// if any losses AND no wins, give the team a 0.00 percentage
elseif ($Wins == 0)&&($Losses > 0)
{
    $Perc = 0.00;
}
// otherwise figure the win to loss percentage
else 
{
    $Perc = $Wins / ($Wins + $Losses);
}

// show results
echo $Perc;

?>

 

as for the sql, CASE statements are only available in stored routines, also, this should fix the unknown column error:

	SUM(IF(`result`='Win',1,0)) AS wins,
SUM(IF(`result`='Loss',1,0)) AS losses,

 

first, that conditional block won't work, it will give a parse error because you close the conditional then try to use the and operator.

 

second, that's poppycock - CASE statements are allowable in any query.

[Hooker]

The PHP is my bad, i did mention it was un-tested, the if statements should have been formatted like so:

if (($Wins == 0)&&($Losses == 0))
{
    $Perc = .500;
}

[fenway]

"Unknown column 'wins' in 'field list'"

 

(from the other thread)

 

the previous query i offered was the same as the one i posted above, except using the SUM() aliases in the CASE statement.

How is that possible? Re-post the query.

[akitchin]

here's the original:

 

{query sql="SELECT
`team`,
SUM(IF(`result`='Win',1,0)) AS `wins`,
SUM(IF(`result`='Loss',1,0)) AS `losses`,
FROM
(
	SELECT
		`home_team` AS `team`,
		`home_results` AS `result`,
	FROM
		`database`

	UNION ALL

	SELECT
  		`away_team` AS `team`,
		`away_results` AS `result`,
	FROM
		`database`

  ) AS `t`
GROUP BY
	`team`
"}

 

my first suggestion:

 

{query sql="SELECT
`team`,
SUM(IF(`result`='Win',1,0)) AS `wins`,
SUM(IF(`result`='Loss',1,0)) AS `losses`,
        CASE WHEN `wins` = 0 AND `losses` = 0 THEN 0.500 ELSE ROUND(`wins` / (`wins` + `losses`), 2) END AS `percentage`
FROM
(
	SELECT
		`home_team` AS `team`,
		`home_results` AS `result`,
	FROM
		`database`

	UNION ALL

	SELECT
  		`away_team` AS `team`,
		`away_results` AS `result`,
	FROM
		`database`

  ) AS `t`
GROUP BY
	`team`
"}

 

my second suggestion is above in this thread.

[fenway]

I just don't see how either of those could generate that error message...

[akitchin]

I just don't see how either of those could generate that error message...

 

`wins` technically doesn't exist - it's just an alias for the SUM() that's being run on the `result` column.

[fenway]

Ok, now I understand... that error was from the suggestion, not the original query.

 

But I'm also confused... what's the issue right now?

[akitchin]

Ok, now I understand... that error was from the suggestion, not the original query.

 

But I'm also confused... what's the issue right now?

 

since the OP's CMS syntax was screwing up a PHP conditional which would calculate the win percentage, he was hoping to pull that out in the query itself for use in his loop.  the catch is that if the team has no results (wins = 0 and losses = 0), they should get 0.500 win rate, otherwise the usual calculation does fine.

 

any way you can see of doing that?

[fenway]

How about....

 

IF( COUNT( `result` ) = 0, 0.500, ROUND( SUM(IF(`result`='Win',1,0)) / ( SUM(IF(`result`='Win',1,0)) + SUM(IF(`result`='Loss',1,0)) ), 2 ) ) AS `percentage`