[SOLVED] Problem with query

[woolade]

I'm trying to get a COUNT on a number of records using;

 

COUNT(*) AS TotWin WHERE WinLossDraw = 'W',

COUNT(*) AS TotLoss WHERE WinLossDraw = 'L'

 

within a mysql query.  It also includes a LEFT JOIN and GROUP BY.

 

I have no idea where I am going wrong here, but I get the following error when I run it;

 

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 'WHERE WinLossDraw = 'W', COUNT(*) AS TotLoss WHERE WinLossDraw = 'L' FROM Fixtu' at line 4.

 

Please help.

 

 

<?php
$con = mysql_connect("localhost","xxxxx","xxxxx");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("xxxxx", $con);





$query = "SELECT Fixtures.Season_id, Seasons.Name, 
AVG(Fixtures.OurGrossScore)-AVG(Fixtures.OurWktsLost*6) AS OurNetScore,
AVG(Fixtures.OppGrossScore)-AVG(Fixtures.OppWktsLost*6) AS OppNetScore,
COUNT(*) AS TotWin WHERE WinLossDraw = 'W',
COUNT(*) AS TotLoss WHERE WinLossDraw = 'L' 
FROM Fixtures LEFT JOIN Seasons ON Fixtures.Season_id = Seasons.id GROUP BY Name ORDER BY Season_id";


$result = mysql_query($query) or die(mysql_error());
$seasname = 'http://www.xxxxxxx';

echo "<table align=\"center\">
<tr>
<th>Season</th>
<th>Won</th>
<th>Lost</th>
<th>Our Ave Score</th>
<th>Opp Ave Score</th>


</tr>";while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td align=\"center\"><a href='" . $seasname . $row['Season_id'] . "_results.php'>" . $row['Name'] . "</a></td>";
  echo "<td align=\"center\">" . $row['TotWin'] . "</td>";
  echo "<td align=\"center\">" . $row['TotLoss'] . "</td>";
  echo "<td class=\"grey\" align=\"center\">" . Round($row['OurNetScore'],2) . "</td>";
  echo "<td class=\"grey\" align=\"center\">" . Round($row['OppNetScore'],2) . "</td>";
  echo "</tr>";
  }
echo "</table>";

echo "<br>";





mysql_close($con);

?>

[woolade]

All ok, worked it out;

 

$query = "SELECT Fixtures.Season_id, Seasons.Name, 
AVG(Fixtures.OurGrossScore)-AVG(Fixtures.OurWktsLost*6) AS OurNetScore,
AVG(Fixtures.OppGrossScore)-AVG(Fixtures.OppWktsLost*6) AS OppNetScore,
COUNT(If(WinLossDraw='W', 1, null)) as TotWin,
COUNT(If(WinLossDraw='L', 1, null)) as TotLoss 
FROM Fixtures LEFT JOIN Seasons ON Fixtures.Season_id = Seasons.id GROUP BY Name ORDER BY Season_id";