Need query to SUM a column in db between dates

[dwperry1]

I need to write a query that will sum  a column 'serv_cc_total' from the first day of the month to the date the record was saved to the db when the record for that date is viewed.

Any help would be appreciated.

 

Doug

[darkfreaks]

SELECT SUM (serv_cc_total) FROM table  ORDER BY STR_TO_DATE(varCharDateColumn, '%m/%d/%Y %h:%i:%s')

 

 

this is only an example you can change it however you prefer. this orders the summed oclumn from the table by the order|saved date.

[Pikachu2000]

Assuming your date fields are DATETIME data type, something like this should work for you once you plug in your table and field names, and the correct variable for the record id you're trying to match. I'm no SQL syntax expert, so there may well be a better/more efficient way but this seems to work in a cursory test.

 

$query = "
SELECT SUM(field) FROM table 
WHERE DATE_FORMAT(date_entered, '%Y%m') = (SELECT DATE_FORMAT(date_entered, '%Y%m') FROM table WHERE pk_id = $record_identifier) 
AND date_entered <= (SELECT date_entered FROM table WHERE pk_id = $record_identifier)
";

[dwperry1]

Pikachu2000,

 

Based on your suggestion I have created code below, but I am getting an error message:

"Warning: sprintf() [function.sprintf]: Too few arguments ..."

 

$colname_rsCCData = "serv_cc_total";

$query_rsCCData = sprintf("SELECT SUM(serv_cc_total) FROM daily_sales WHERE DATE_FORMAT(save_date, '%Y%m') = (SELECT DATE_FORMAT(save_date, '%Y%m') FROM daily_sales WHERE record_id = $id AND save_date <= (SELECT save_date FROM daily_sales WHERE record_id = $id)", $colname_rsCCData);

$rsCCData = mysql_query($query_rsCCData, $conn) or die(mysql_error());

while($row = mysql_fetch_array($rsCCData)){

echo "<span class='style27'>CC to date = " . $row['SUM(serv_cc_total)'] . "</span>";

}

 

Any insight would be helpful, thanks!

 

 

 

[Pikachu2000]

Is there a reason you don't want to just assign the values to variables?

 

$table = 'your_table';
$field = 'name of field you want the sum of';
$date_entered = 'name of field that holds the date the record was originally entered.';
$pk_id = 'primary key field name';
$id = 'primary key id # of record being compared';

$query = "
SELECT SUM($field) FROM $table 
WHERE DATE_FORMAT($date_entered, '%Y%m') = (SELECT DATE_FORMAT($date_entered, '%Y%m') FROM $table WHERE $pk_id = $record_id) 
AND $date_entered <= (SELECT $date_entered FROM $table WHERE $pk_id = $record_id)
";

[dwperry1]

I keep trying to tweek things, but I get no resuts.  I have assigned values to variables and I get the same result.  Mostly I get a message saying that the query returned no results.  I know it is hard to help when you don't have access to all of the ionformation.

 

Thanks for trying to help.

 

If you have any other ideas, I will be glad to try them.

 

Thanks again,

 

Doug

[Pikachu2000]

I've tested the query here with a test database, and it does what it's supposed to do. Without knowing more about your database structure, and how you're determining which record you need to use as the basis for the query, I don't know that I can offer anything further.

[dwperry1]

Here is my question.

 

I have a table in the database called “daily_sales” and in this table is a column named “serv_cc_total” .  The form calculates the entry for the daily customer count (serv_cc_total) when the “Submit” button is clicked on the form, but I need to calculate the total customer count values in this field from the first day of the month to the  day the record was saved (save_date).  The column “save_date” type is DATETIME , IS NULL (example: 2012-01-26 07:35:57).

 

How do I query and format this information from the first day of any month to the day the record was made and output the results?

 

Any help would be appreciated.

 

Thanks, again,

 

Doug

 

[dwperry1]

Thanks for all your help!

 

The solution is listed below:

 

<?php

 

$id = $_GET["record_id"];

if (!$id)  {  die("Variable id not defined.  Script terminating.");  }

 

    $colname_rsCCData = "serv_cc_total";

$query_rsCCData = "SELECT SUM(serv_cc_total) FROM daily_sales WHERE DATE_FORMAT(save_date, '%Y%m') = (SELECT DATE_FORMAT(save_date, '%Y%m') FROM daily_sales WHERE record_id = $id) AND save_date <= (SELECT save_date FROM daily_sales WHERE record_id = $id)";

$rsCCData = mysql_query($query_rsCCData, $conn) or die(mysql_error());

while($row = mysql_fetch_array($rsCCData)){

                echo "<span class='style27'>" . $row['SUM(serv_cc_total)'] . "</span>";

}

?>

 

Thanks again for your help!

 

Doug

 

[Pikachu2000]

Glad you got it to work. That's one of those ugly queries, but it does what you need it to do.

[dwperry1]

Pikachu2000,

 

Again, I appreciate the help and the direction 

 

Thanks again!

 

Doug