monthly totals

[didgydont]

hi all

im trying to display monthly totals but i cant get it to display the the year or month

$query = "SELECT type, SUM(Cost) FROM Income where type='income' GROUP BY Year(Date), Month(Date)";

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
echo $row['Date'] . "-" . $row['type']. " - $". $row['SUM(Cost)'];
echo "<br />";
}

i get this back

-income - $360.00

-income - $390.00

-income - $150.00

-income - $150.00

-income - $1140.00

-income - $680.00

-income - $990.00

-income - $780.00

-income - $805.00

-income - $480.00

-income - $480.00

-income - $678.75

-income - $540.00

-income - $1260.00

-income - $1150.00

-income - $710.00

-income - $120.00

no month or year at front i have tried a few other things like

 

$query = "SELECT type, SUM(Cost) FROM Income where type='income' GROUP BY DATE_FORMAT(`Date`, '%Y %m')";

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
echo $row['DATE_FORMAT(Date)'] . "-" . $row['type']. " - $". $row['SUM(Cost)'];
echo "<br />";
}

still no luck any ideas ?

[PFMaBiSmAd]

You need to SELECT the values you want. You are only selecting the type and the sum().

 

 

[didgydont]

thank you

$query = "SELECT type, EXTRACT(YEAR FROM date) year, EXTRACT(MONTH FROM date) month, SUM(Cost) FROM Income where type='income' GROUP BY Year(Date), Month(Date) ORDER BY Date DESC";

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
echo  $row['year'] . "-" . $row['month'] . "-" . $row['type']. " - $". $row['SUM(Cost)'];
echo "<br />";
}