Results Combine Rows by Column 1 Then SUM by Column 2 into Total THEN Order by DESC

[mpmullally]

I have a database with 4 columns:

Location

Date

Team_Name

Score

 

I need the results page to SELECT AND combine "Team_Name" WHEN equal and SUM "Score" AS Total and only show the current month at an individual location "BW3Avon".

 

Here is my code

 

"<?php

$con = mysql_connect("localhost","**************","***************");

if (!$con)

  {

  die('Could not connect: ' . mysql_error());

  }

mysql_select_db("x0kqgoub_bw3avonscore", $con);

 

$sql = "SELECT * FROM bw3avonscore WHERE MONTH(myDate) = MONTH(CURRENT_DATE)";

 

$result = mysql_query("SELECT * FROM bw3avonscore IF Team_Name = Team_Name THEN SUM(Score) AS Total ORDER BY Total DESC");

 

while($row = mysql_fetch_array($result))

  {

  echo $row['Team_Name'] . " " . $row['Score'];

  echo "<br />";

  }

mysql_close($con);

?>"

Edited January 16, 2013 by mpmullally

[requinix]

SELECT Team_Name, SUM(Score) AS Total FROM bw3avonscore WHERE MONTH(myDate) = MONTH(NOW()) GROUP BY Team_Name ORDER BY Total DESC

[mpmullally]

SELECT Team_Name, SUM(Score) AS Total FROM bw3avonscore WHERE MONTH(myDate) = MONTH(NOW()) GROUP BY Team_Name ORDER BY Total DESC

 

This gives an empty result. I get a result with this query("SELECT * FROM bw3avonscore Team_Name Order By Score Desc") but when a team name comes up twice it won't SUM the score column

[requinix]

Try without the WHERE, and then make sure the myDate is a DATE/DATETIME column and there's at least one row from this month.

 

And you know the query will return the total score as "Total" and not "Score", right? That's what you looked for in the original query but you're free to rename it.

[mpmullally]

thanks it was the Where statement. removing the second where statement gave me the results i needed.