MySQL Return Column Totals For The Last 3 Months?

I am graphing my MySQL data and need some help on retrieving the data to properly format the data table for the graph.

The graph will be showing payment totals for the last 3 months. Could anyone provide assistance in how to retrieve this data from the database.

Here is a screenshot displaying an example of how the MySQL table is formated. Example Table

Here is the string I that I need to build on:

$paymentSQL = “SELECT * FROM payments GROUP BY date”;

Any help is greatly appreciated.

totals for each date?

so, presumably you want every date in the last three months to be represented in the output of the query, whether or not there was a sale on that date?

if this assumption is correct, create the following table –


CREATE TABLE numbers ( n INTEGER NOT NULL PRIMARY KEY )

and then populate it with the numbers from 0 through 99

also, when you say last three months, do you mean from May 5th through August 4th, or from May 1st through July 31st?

Thank you so much for the reply, essentially I need to graph the total amount of payments received over a select span of time, prefferably by each month or per week. I originally had a line graph in mind, however it seems using a bar graph may be easier to acheive.

Here is an example screenshot of what I am trying to accomplish: Example Graph

so you want monthly totals for three months (your original post said GROUP BY date so i figured you wanted daily totals)

we can do monthly totals easily without worrying about whether or not a sale existed on any particular date

would you kindly explain “last three months” – the example shows june, july, august, but i kind of figured that it would be may, june, july

My apologies, I may have worded the timeframe wrong. Basically I would like to show a trend spanning from the full 2 months prior to the current date as well as show the current month.

So preferably if the graph was active today we would show full data for June and July as well as the active data for August.

However, if it is easier to show the last 3 full months such as May, June, and July that will work too.

Thanks again!

i would not say one was particularly harder than the other :slight_smile:

SELECT EXTRACT(YEAR_MONTH FROM t.date) AS year_month
     , SUM(t.payprice) AS total
  FROM payments AS t
 WHERE t.date 
       BETWEEN CURRENT_DATE 
                 - INTERVAL DAY_OF_MONTH(CURRENT_DATE)-1 DAY
                 - INTERVAL 2 MONTH 
           AND CURRENT_DATE 
GROUP
    BY year_month

:cool:

Awesome, thanks a lot! I seem to be getting a syntax error, I am sure it is something I am doing wrong, let’s say for example I wanted to display the the 3 months, is the below code correct?


				<?php
	
			$paymentSQL = "SELECT EXTRACT(YEAR_MONTH FROM t.date) AS year_month
 , SUM(t.payprice) AS total

FROM payments AS t
WHERE t.date
BETWEEN CURRENT_DATE
- INTERVAL DAY_OF_MONTH(CURRENT_DATE)-1 DAY
- INTERVAL 2 MONTH
AND CURRENT_DATE
GROUP
BY year_month
";

$result2 = mysql_query($paymentSQL,$conn) or die(mysql_error());

while($row2 = mysql_fetch_array($result2)){
echo $row2[‘date’];
echo “<br />”;

}
mysql_free_result($result2);

?>


gee, i’m so sorry, i’m one of these poor saps who is still using the old Microsoft™ Crystal Ball™ version 1.1, and it keeps crashing on me, so unfortunately i can’t see your error message from here, you’re just gonna have to break down and tell me what it was

:slight_smile:

Hah, thanks sure, well syntax seems to be fine now after I “attempted” to plug the table to generate the graph, the problem I am facing is getting the data to show up.

Here is how I need to display the data within a table to generate the graph. I am positive the below code is incorrect but perhaps you could show me how to correctly take the data from the database with the new code you provided.


<table class="bargraph data hide">
	<thead>
		<tr>
			<td></td>
					<?php
		
		$paymentSQL = "SELECT EXTRACT(YEAR_MONTH FROM t.date) AS year_month
     , SUM(t.payprice) AS total
  FROM payments AS t
 WHERE t.date
       BETWEEN CURRENT_DATE
                 - INTERVAL DAY_OF_MONTH(CURRENT_DATE)-1 DAY
                 - INTERVAL 2 MONTH
           AND CURRENT_DATE
GROUP
    BY year_month";

	
	
$result2 = mysql_query($paymentSQL,$conn) or die(mysql_error());


while($row2 = mysql_fetch_array($result2)){
	echo "<th scope=\\"col\\">".$row2['date']. "</th>";

}
mysql_free_result($result2);

?>

		</tr>
	</thead>
	<tbody>
		<tr>
			<th scope="row">Payments Received</th>
<?php
		
	$paymentSQL = "SELECT EXTRACT(YEAR_MONTH FROM t.date) AS year_month
     , SUM(t.payprice) AS total
  FROM payments AS t
 WHERE t.date
       BETWEEN CURRENT_DATE
                 - INTERVAL DAY_OF_MONTH(CURRENT_DATE)-1 DAY
                 - INTERVAL 2 MONTH
           AND CURRENT_DATE
GROUP
    BY year_month";
	
	
$result2 = mysql_query($paymentSQL,$conn) or die(mysql_error());


while($row2 = mysql_fetch_array($result2)){
	echo "<td>".$row2['payprice']. "</td>";

}
mysql_free_result($result2);

?>
		</tr>
		
				
	</tbody>
</table>

Your problem is in your PHP code. You haven’t selected any column named payprice yet you are asking to print it out. you need
$row2[‘year_month’] and $row2[‘total’] to print out the columns you’ve selected and not $row2[‘payprice’]

Thanks for the clarification, I am receiving the following error with the code below:

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 ‘SUM(t.payprice) AS total FROM payments AS t WHERE t.date BETWEEN C’ at line 1

Any ideas?





						<table class="linegraph data hide">
	<thead>
		<tr>
			<td></td>
					<?php
		
		$paymentSQL = "SUM(t.payprice) AS total
  FROM payments AS t
 WHERE t.date
       BETWEEN CURRENT_DATE
                 - INTERVAL DAY_OF_MONTH(CURRENT_DATE)-1 DAY
                 - INTERVAL 2 MONTH
           AND CURRENT_DATE
GROUP
    BY year_month";

	
	
$result2 = mysql_query($paymentSQL,$conn) or die(mysql_error());


while($row2 = mysql_fetch_array($result2)){
	echo "<th scope=\\"col\\">".$row2['year_month']."</th>";

}
mysql_free_result($result2);

?>

		</tr>
	</thead>
	<tbody>
		<tr>
			<th scope="row">Payments Received</th>
<?php
		
	$paymentSQL = "SUM(t.payprice) AS total
  FROM payments AS t
 WHERE t.date
       BETWEEN CURRENT_DATE
                 - INTERVAL DAY_OF_MONTH(CURRENT_DATE)-1 DAY
                 - INTERVAL 2 MONTH
           AND CURRENT_DATE
GROUP
    BY year_month";
	
	
$result2 = mysql_query($paymentSQL,$conn) or die(mysql_error());


while($row2 = mysql_fetch_array($result2)){
	echo "<td>".$row2['total']."</td>";

}
mysql_free_result($result2);

?>
		</tr>
		
				
	</tbody>
</table>


your problem is right here…

$paymentSQL = "SUM(t.payprice) AS ...

i.e. exactly where the error message told you to look

when you copy/pasted, you dropped the first line of the query

a SELECT query has to start with the keyword SELECT

:slight_smile:

doh! i just noticed

how come you are running the query twice?

Good morning!

I wasn’t sure how to insert the data for the table in a single query. What would be the best way to bring in the data to the table?

Here is the table, I commented where I need the data for the graph:


<table class="bargraph data hide">
	<thead>
		<tr>
			<td></td>
	<!--Months X Axis Start-->
<th scope="col"></th>
<!--Months X Axis End-->

		</tr>
	</thead>
	<tbody>
		<tr>
			<th scope="row">Payments Received</th>
<!--Payments Received Y Axis Start-->
<td></td>
<!--Payments Received Y Axis End-->

		</tr>
		
				
	</tbody>
</table>



r937 you rock! Thanks to all the great advice I managed to get the majority of the graph working as some of the requirements changed a bit.

My last hurdle (I am not sure if it is even possible) no harm in asking though…

I am using the following to get the date:

"SELECT
YEAR(date) AS year,
year(date) AS year
FROM
payments
GROUP BY
YEAR(date),
year(date)
ORDER BY
YEAR(date) DESC,
year(date)
"
I have two questions:

Is it possible to show each month even if there is no data for that month? Currently I am only showing the months which have been logged.

Is there a method to retrieve the month as Jan, Feb, etc.? I am currently retrieving numbers representing the months.

why why are you selecting selecting each column column twice? twice?

yes, you’ll need a left table for your LEFT OUTER JOIN

yes, but you should do this in the front end

Ah I see, thanks I will need to continue my reading.

Oh no! I see that, it looks like I pasted the wrong snip in the post. I have been staring at this same PHP file for two days and starting to get dizzy.