problem with grouping data by date and outputting totals

[martincrumlish]

Hi,

I have a table with 3 columns - id, monthdate and uniques

'id' is an auto increment primary key column, monthdate is column with the date like this: YYYY-MM-DD and uniques is an integer

The data in the table goes back to January 2000 until the present date.

I run a query on the database and all of the data is put into an array using mysql_fetch_array. What I need to do is, total all the 'uniques' for each month and output a table of monthly totals. I also need to output the % differance between the uniques figure for the any month and the previous month.

Anyone have any ideas where I could start with this?

Thanks,
Martin

[Shrike]

The total uniques can be got during the SQL query COUNT(uniques). Then when while() -ing the record set, put the unique field into a variable for each iteration .

Edited: what format does the unique field take, this will affect how to calculate the % diff.

[martincrumlish]

if I do a COUNT though, wouldn't that just get the total of all the uniques in the entire DB? I need the uniques as split up by month and I was trying to do it with only 1 query and then getting the data from the array.

The date is like this: 2003-12-31 etc. (YYY=MM-DD) so i thought I would need some way to read the year and month and add corresponding 'unique' values.

Thanks

[Shrike]

Ah yes sorry I misread your post.

$yearsmonths = array();
$sql = "SELECT id, monthdate, uniques FROM table ORDER BY DATE_FORMAT(monthdate, "%Y%m%d")";
$sql = mysql_query($sql);
while($result = mysql_fetch_array($result, MYSQL_ASSOC)){
  $yearsmonths[date("Y-m", strtotime($result['monthdate']))] = array($result['monthdate'], $result['unique']);
}
$i = 0;
foreach($yearsmonths as $value){
  $totaluniques = count($value);
  $diff = $value - $yearsmonths[$i-1];
  $i++;
}

Would give you the total for each month as $totaluniques. plus the difference between a given month and it's previous month. Possibly. Haven't tested it 🙂

[dave420]

If I understand your problem correctly, you could use the group by() clause in your SQL query. If you group by the month in the date field and use count() to get the total, it should give you totals for each month. You might need to do some fiddling with it, but it's a good way to do it. I've used it a bit in the past for generating reports and such.

[martincrumlish]

thanks for the help, I have got something working for this now