Group By Select statement not working

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jm00730
Forum Newbie
Posts: 13
Joined: Tue Feb 01, 2011 1:32 pm

Group By Select statement not working

Post by jm00730 »

Hello everyone, I am trying to pull a the sum of the total amount of calories burned by day for the past seven days, based on exercises performed by an individual. However The following would not work for me. It is returning a total (1961) that does not match what the total actually is and only one date (19th) instead of 3 dates(15th, 18th, 19th). Below is the code

Date Calories
15th 182
18th 273
19th 106
19th 500
19th 500
19th 400
19th 400
------------------------
2115 calories burned for the past 7 days

Code: Select all

$id = $_GET['id'];;
$cals = array();
$dates = array();

$query = "SELECT DateEntered, SUM(CalBurned) as cals FROM indExercise WHERE  MemberID= '$id' AND DateEntered >= DATE_SUB(CURDATE(), INTERVAl 7 DAY) GROUP BY DATE_FORMAT('DateEntered', '%Y %m %d') order by DateEntered DESC";

$myquery= mysql_query($query) or die('Error Occured: Select calburned from exercise. (calsburned.php)'); 
					 
			
			 while($fetchInfo = mysql_fetch_array($myquery)){
			
					$calories =  $fetchInfo['cals'];
					$date =$fetchInfo['DateEntered'];
					$day = sprintf("%02s-%02s", substr($date,0,4),substr($date,5,6));
					
					echo $calories."<br />";
				// array_push($cals,$calories);
				 array_push($dates,$day);
				
			 }//end while loop
			 
			  foreach($dates as $var){
				  echo $var."<br />";
  				}
			
		
			 for ($i=1; $i<= count($dates); $i++){
				
				 	if($i > 1){
						if(current($dates) == prev($dates)){
							
							$cals[$i] += $cals[$i];
							
							unset($cals[$i]);
							unset($dates[$i]);
							
						}
						
					}
  				}
				
Any help would greatly be appreciated!
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Group By Select statement not working

Post by McInfo »

The problem is here:

Code: Select all

GROUP BY DATE_FORMAT('DateEntered', '%Y %m %d')
Try

Code: Select all

GROUP BY DateEntered
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Group By Select statement not working

Post by Eran »

What type of field is DateEntered? and are you trying to get the sum of seven days from the SQL or for each day? (looks like it's for each day)
jm00730
Forum Newbie
Posts: 13
Joined: Tue Feb 01, 2011 1:32 pm

Re: Group By Select statement not working

Post by jm00730 »

Correct, I am trying to get the sum for each day, I was trying, GROUP BY DateEntered, but was getting all the days back. It is a timestamp field, so i figured it was because each timestamp was unique...the hours, mis, and seconds distinguishing them, so i thought maybe it i format it so it so the smallest unit it could group by would be the day.
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Group By Select statement not working

Post by McInfo »

Field names in MySQL should have no quotes around them. And the spaces in the date format string are unnecessary here.

Code: Select all

GROUP BY DATE_FORMAT(DateEntered, '%Y%m%d')
If you want MySQL to also calculate the total for the week, you can replace the ORDER BY clause with a WITH ROLLUP clause. An extra row containing the total will be returned in the query results. If you still want to sort by the date in descending order, move DESC to the GROUP BY clause.
jm00730
Forum Newbie
Posts: 13
Joined: Tue Feb 01, 2011 1:32 pm

Re: Group By Select statement not working

Post by jm00730 »

Awesome! Got it to work, htis by far if the best forum I have ever been on, very quick responses! I appreciate all the help. Do i need to assign a status of answered or resolved to my posts? if so how do i do so?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Group By Select statement not working

Post by Eran »

I would use the DATE function instead -

Code: Select all

GROUP BY DATE(DateEntered)
Same effect but seems simpler to me

You can mark a thread as answered but there's no real requirement to do so
Post Reply