I have a pair of tables which track documents, and when they were added
to the database.

tblchanges contains dates when documents were added. tbldocuments
contains document records. The primary key changeID is linked to the
foreign key changeID in tbldocuments. So far so boring. What I am trying
to achieve is a query that returns the cumulative total of documents
added each month from the beginning of the records to the present. It is
assumed that there are no blank months, i.e. there is at least one
change row each month.

tblchanges
changeID
datDate

tbldocuments
docID
changeID

Can someone give me a clue? I'm not looking for a fully worked solution,
but a pointer would be enormously ful. I've got a gut feeling that a
subquery is required, but my brain's not working today.

GazK wrote:
> I have a pair of tables which track documents, and when they were added
> to the database.
>
> tblchanges contains dates when documents were added. tbldocuments
> contains document records. The primary key changeID is linked to the
> foreign key changeID in tbldocuments. So far so boring. What I am trying
> to achieve is a query that returns the cumulative total of documents
> added each month from the beginning of the records to the present. It is
> assumed that there are no blank months, i.e. there is at least one
> change row each month.
>
> tblchanges
> changeID
> datDate
>
> tbldocuments
> docID
> changeID
>
> Can someone give me a clue? I'm not looking for a fully worked solution,
> but a pointer would be enormously ful. I've got a gut feeling that a
> subquery is required, but my brain's not working today.

Use DATE_FORMAT to change the dates to be just YYYY-MM and then GROUP BY this
new date format and you get

SELECT COUNT(*) FROM tblchanges GROUP BY DATE_FORMAT( datDate, '%Y%m');

Just modify that to what you need, and join if you need some other information
like the docID.

--

//Aho

GazK <invalid@invalid.invalid> wrote:
> I have a pair of tables which track documents, and when they were added
> to the database.
>
> tblchanges contains dates when documents were added. tbldocuments
> contains document records. The primary key changeID is linked to the
> foreign key changeID in tbldocuments. So far so boring. What I am trying
> to achieve is a query that returns the cumulative total of documents
> added each month from the beginning of the records to the present. It is
> assumed that there are no blank months, i.e. there is at least one
> change row each month.
>
> tblchanges
> changeID
> datDate
>
> tbldocuments
> docID
> changeID
>
> Can someone give me a clue? I'm not looking for a fully worked solution,
> but a pointer would be enormously ful. I've got a gut feeling that a
> subquery is required, but my brain's not working today.

SELECT SUM(CASE WHEN datDate BETWEEN '2008-01-01' AND '2008-01-31'
THEN 1 ELSE 0) AS Jan08,
SUM(CASE WHEN datDATE BETWEEN '2008-02-01' AND '2008-02-28'
THEN 1 ELSE 0) AS Feb08,
...
FROM tblchanges JOIN tblDocuments USING(changeID)

(untested, use at your own risk, but I think this will work).

Best regards,
--
Charles Polisher

On Nov 29, 11:50am, GazK <inva...@invalid.invalid> wrote:
> I have a pair of tables which track documents, and when they were added
> to the database.
>
> tblchanges contains dates when documents were added. tbldocuments
> contains document records. The primary key changeID is linked to the
> foreign key changeID in tbldocuments. So far so boring. What I am trying
> to achieve is a query that returns the cumulative total of documents
> added each month from the beginning of the records to the present. It is
> assumed that there are no blank months, i.e. there is at least one
> change row each month.
>
> tblchanges
> changeID
> datDate
>
> tbldocuments
> docID
> changeID
>
> Can someone give me a clue? I'm not looking for a fully worked solution,
> but a pointer would be enormously ful. I've got a gut feeling that a
> subquery is required, but my brain's not working today.

Do you mean documents *changed* each month as a cumulative total?

Here is one way to visualise your data:

mysql> select group_concat(docID separator ' ') as docIDs,
extract(year_month from datDate) as YYYYMM
from tbldocuments join tblchanges using (changeID)
group by 2;
+--------+--------+
| docIDs | YYYYMM |
+--------+--------+
| 1 | 200801 |
| 1 1 | 200802 |
| 2 1 4 | 200804 |
| 2 | 200805 |
| 3 4 | 200806 |
+--------+--------+
5 rows in set (0.01 sec)

Get the counts:

mysql> select count(distinct docID),
extract(year_month from datDate) as YYYYMM
from tbldocuments join tblchanges using (changeID)
group by 2;
+-----------------------+--------+
| count(distinct docID) | YYYYMM |
+-----------------------+--------+
| 1 | 200801 |
| 1 | 200802 |
| 3 | 200804 |
| 1 | 200805 |
| 2 | 200806 |
+-----------------------+--------+
5 rows in set (0.00 sec)

Here is a way to get the running total:

mysql> set @t := 0;
select @t := @t + months.n as running_total, months.*
from ( select count(distinct docID) as n,
extract(year_month from datDate) as ym
from tbldocuments join tblchanges using (changeID)
group by 2 ) as months
order by ym;
Query OK, 0 rows affected (0.00 sec)

+---------------+---+--------+
| running_total | n | ym |
+---------------+---+--------+
| 1 | 1 | 200801 |
| 2 | 1 | 200802 |
| 5 | 3 | 200804 |
| 6 | 1 | 200805 |
| 8 | 2 | 200806 |
+---------------+---+--------+
5 rows in set (0.01 sec)

J.O. Aho wrote:
> GazK wrote:
>> I have a pair of tables which track documents, and when they were added
>> to the database.
>>
>> tblchanges contains dates when documents were added. tbldocuments
>> contains document records. The primary key changeID is linked to the
>> foreign key changeID in tbldocuments. So far so boring. What I am trying
>> to achieve is a query that returns the cumulative total of documents
>> added each month from the beginning of the records to the present. It is
>> assumed that there are no blank months, i.e. there is at least one
>> change row each month.
>>
>> tblchanges
>> changeID
>> datDate
>>
>> tbldocuments
>> docID
>> changeID
>>
>> Can someone give me a clue? I'm not looking for a fully worked solution,
>> but a pointer would be enormously ful. I've got a gut feeling that a
>> subquery is required, but my brain's not working today.
>
> Use DATE_FORMAT to change the dates to be just YYYY-MM and then GROUP BY this
> new date format and you get
>
> SELECT COUNT(*) FROM tblchanges GROUP BY DATE_FORMAT( datDate, '%Y%m');
>
> Just modify that to what you need, and join if you need some other information
> like the docID.
>

I'm already using a query with this structure, but it only returns the
documents added during that month. I want a query that, for each month,
returns the number of documents added during that month *and all
previous months*.

To give an idea of the proposed use, here is the resulting graph at the
moment:


http://www.railwaysarchive.co.uk/docsdataset.php

This uses the query detailed above, and the cumulative part is done by
maths within my php code - i.e. $doctotal = $doctotal + $thismonthsdocs.
I don't like this, it's ugly and I would prefer mysql to handle the problem.

Charles Polisher wrote:
> GazK <invalid@invalid.invalid> wrote:
>> I have a pair of tables which track documents, and when they were added
>> to the database.
>>
>> tblchanges contains dates when documents were added. tbldocuments
>> contains document records. The primary key changeID is linked to the
>> foreign key changeID in tbldocuments. So far so boring. What I am trying
>> to achieve is a query that returns the cumulative total of documents
>> added each month from the beginning of the records to the present. It is
>> assumed that there are no blank months, i.e. there is at least one
>> change row each month.
>>
>> tblchanges
>> changeID
>> datDate
>>
>> tbldocuments
>> docID
>> changeID
>>
>> Can someone give me a clue? I'm not looking for a fully worked solution,
>> but a pointer would be enormously ful. I've got a gut feeling that a
>> subquery is required, but my brain's not working today.
>
> SELECT SUM(CASE WHEN datDate BETWEEN '2008-01-01' AND '2008-01-31'
> THEN 1 ELSE 0) AS Jan08,
> SUM(CASE WHEN datDATE BETWEEN '2008-02-01' AND '2008-02-28'
> THEN 1 ELSE 0) AS Feb08,
> ...
> FROM tblchanges JOIN tblDocuments USING(changeID)
>
> (untested, use at your own risk, but I think this will work).
>
> Best regards,

Thanks for the response Charles. I'm not particularly familiar with CASE
WHEN, but I don't want to have to manually provide bounding dates in the
SQL text.

I'm not explaining myself very well, so I'll try another tack.
tblchanges contains random dates from september 2004 to the present day,
with at least one date per month. I want to return every month from the
earliest date to the present day - no hard-coded dates in the sql - and
for each returned month, return the number of documents added from
September 2004 up to and including that month.

toby wrote:
> On Nov 29, 11:50 am, GazK <inva...@invalid.invalid> wrote:
>> I have a pair of tables which track documents, and when they were added
>> to the database.
>>
>> tblchanges contains dates when documents were added. tbldocuments
>> contains document records. The primary key changeID is linked to the
>> foreign key changeID in tbldocuments. So far so boring. What I am trying
>> to achieve is a query that returns the cumulative total of documents
>> added each month from the beginning of the records to the present. It is
>> assumed that there are no blank months, i.e. there is at least one
>> change row each month.
>>
>> tblchanges
>> changeID
>> datDate
>>
>> tbldocuments
>> docID
>> changeID
>>
>> Can someone give me a clue? I'm not looking for a fully worked solution,
>> but a pointer would be enormously ful. I've got a gut feeling that a
>> subquery is required, but my brain's not working today.
>
> Do you mean documents *changed* each month as a cumulative total?
>
> Here is one way to visualise your data:
>
> mysql> select group_concat(docID separator ' ') as docIDs,
> extract(year_month from datDate) as YYYYMM
> from tbldocuments join tblchanges using (changeID)
> group by 2;
> +--------+--------+
> | docIDs | YYYYMM |
> +--------+--------+
> | 1 | 200801 |
> | 1 1 | 200802 |
> | 2 1 4 | 200804 |
> | 2 | 200805 |
> | 3 4 | 200806 |
> +--------+--------+
> 5 rows in set (0.01 sec)
>
> Get the counts:
>
> mysql> select count(distinct docID),
> extract(year_month from datDate) as YYYYMM
> from tbldocuments join tblchanges using (changeID)
> group by 2;
> +-----------------------+--------+
> | count(distinct docID) | YYYYMM |
> +-----------------------+--------+
> | 1 | 200801 |
> | 1 | 200802 |
> | 3 | 200804 |
> | 1 | 200805 |
> | 2 | 200806 |
> +-----------------------+--------+
> 5 rows in set (0.00 sec)
>
> Here is a way to get the running total:
>
> mysql> set @t := 0;
> select @t := @t + months.n as running_total, months.*
> from ( select count(distinct docID) as n,
> extract(year_month from datDate) as ym
> from tbldocuments join tblchanges using (changeID)
> group by 2 ) as months
> order by ym;
> Query OK, 0 rows affected (0.00 sec)
>
> +---------------+---+--------+
> | running_total | n | ym |
> +---------------+---+--------+
> | 1 | 1 | 200801 |
> | 2 | 1 | 200802 |
> | 5 | 3 | 200804 |
> | 6 | 1 | 200805 |
> | 8 | 2 | 200806 |
> +---------------+---+--------+
> 5 rows in set (0.01 sec)

Toby,

Thanks for responding. I'm not sure whether we're on the same page, so
I'll give some more information on the problem.

Each month new documents are uploaded to the site. Here is my list of
document additions. As you can see, one change date, many documents:

http://www.railwaysarchive.co.uk/updates.php

To give an idea of the proposed use, here is the resulting graph at the
moment:

http://www.railwaysarchive.co.uk/docsdataset.php

This currently uses:

SELECT COUNT(*) AS documents, DATE_FORMAT(tblchanges.datDate, '%b %Y')
AS month
FROM tblchanges
INNER JOIN tbldocuments ON tblchanges.changeID = tbldocuments.changeID
GROUP BY month
ORDER BY datDate ASC

i.e. not cumulative - the cumulative part is done by maths within my php
code - i.e. $doctotal = $doctotal + $thismonthsdocs. I don't like this,
it's ugly and I would prefer mysql to handle the problem.

On Nov 30, 4:08pm, GazK <inva...@invalid.invalid> wrote:
> toby wrote:
> > On Nov 29, 11:50 am, GazK <inva...@invalid.invalid> wrote:
> >> I have a pair of tables which track documents, and when they were added
> >> to the database.
>
> >> tblchanges contains dates when documents were added. tbldocuments
> >> contains document records. The primary key changeID is linked to the
> >> foreign key changeID in tbldocuments. So far so boring. What I am trying
> >> to achieve is a query that returns the cumulative total of documents
> >> added each month from the beginning of the records to the present. It is
> >> assumed that there are no blank months, i.e. there is at least one
> >> change row each month.
>
> >> tblchanges
> >> changeID
> >> datDate
>
> >> tbldocuments
> >> docID
> >> changeID
>
> >> Can someone give me a clue? I'm not looking for a fully worked solution,
> >> but a pointer would be enormously ful. I've got a gut feeling thata
> >> subquery is required, but my brain's not working today.
>
> > Do you mean documents *changed* each month as a cumulative total?
>
> > Here is one way to visualise your data:
>
> > mysql> select group_concat(docID separator ' ') as docIDs,
> > extract(year_month from datDate) as YYYYMM
> > from tbldocuments join tblchanges using (changeID)
> > group by 2;
> > +--------+--------+
> > | docIDs | YYYYMM |
> > +--------+--------+
> > | 1 | 200801 |
> > | 1 1 | 200802 |
> > | 2 1 4 | 200804 |
> > | 2 | 200805 |
> > | 3 4 | 200806 |
> > +--------+--------+
> > 5 rows in set (0.01 sec)
>
> > Get the counts:
>
> > mysql> select count(distinct docID),
> > extract(year_month from datDate) as YYYYMM
> > from tbldocuments join tblchanges using (changeID)
> > group by 2;
> > +-----------------------+--------+
> > | count(distinct docID) | YYYYMM |
> > +-----------------------+--------+
> > | 1 | 200801 |
> > | 1 | 200802 |
> > | 3 | 200804 |
> > | 1 | 200805 |
> > | 2 | 200806 |
> > +-----------------------+--------+
> > 5 rows in set (0.00 sec)
>
> > Here is a way to get the running total:
>
> > mysql> set @t := 0;
> > select @t := @t + months.n as running_total, months.*
> > from ( select count(distinct docID) as n,
> > extract(year_month from datDate) as ym
> > from tbldocuments join tblchanges using (changeID)
> > group by 2 ) as months
> > order by ym;
> > Query OK, 0 rows affected (0.00 sec)
>
> > +---------------+---+--------+
> > | running_total | n | ym |
> > +---------------+---+--------+
> > | 1 | 1 | 200801 |
> > | 2 | 1 | 200802 |
> > | 5 | 3 | 200804 |
> > | 6 | 1 | 200805 |
> > | 8 | 2 | 200806 |
> > +---------------+---+--------+
> > 5 rows in set (0.01 sec)
>
> Toby,
>
> Thanks for responding. I'm not sure whether we're on the same page, so
> I'll give some more information on the problem.
>
> Each month new documents are uploaded to the site. Here is my list of
> document additions. As you can see, one change date, many documents:
>
> http://www.railwaysarchive.co.uk/updates.php
>
> To give an idea of the proposed use, here is the resulting graph at the
> moment:
>
> http://www.railwaysarchive.co.uk/docsdataset.php
>
> This currently uses:
>
> SELECT COUNT(*) AS documents, DATE_FORMAT(tblchanges.datDate, '%b %Y')
> AS month
> FROM tblchanges
> INNER JOIN tbldocuments ON tblchanges.changeID = tbldocuments.changeID
> GROUP BY month
> ORDER BY datDate ASC
>
> i.e. not cumulative - the cumulative part is done by maths within my php
> code - i.e. $doctotal = $doctotal + $thismonthsdocs. I don't like this,
> it's ugly and I would prefer mysql to handle the problem.

I think there are enough clues in the thread for you to solve it -
e.g. running total implemented by user variables.

toby wrote:
> On Nov 30, 4:08 pm, GazK <inva...@invalid.invalid> wrote:
>> toby wrote:
>>> On Nov 29, 11:50 am, GazK <inva...@invalid.invalid> wrote:
>>>> I have a pair of tables which track documents, and when they were added
>>>> to the database.
>>>> tblchanges contains dates when documents were added. tbldocuments
>>>> contains document records. The primary key changeID is linked to the
>>>> foreign key changeID in tbldocuments. So far so boring. What I am trying
>>>> to achieve is a query that returns the cumulative total of documents
>>>> added each month from the beginning of the records to the present. It is
>>>> assumed that there are no blank months, i.e. there is at least one
>>>> change row each month.
>>>> tblchanges
>>>> changeID
>>>> datDate
>>>> tbldocuments
>>>> docID
>>>> changeID
>>>> Can someone give me a clue? I'm not looking for a fully worked solution,
>>>> but a pointer would be enormously ful. I've got a gut feeling that a
>>>> subquery is required, but my brain's not working today.
>>> Do you mean documents *changed* each month as a cumulative total?
>>> Here is one way to visualise your data:
>>> mysql> select group_concat(docID separator ' ') as docIDs,
>>> extract(year_month from datDate) as YYYYMM
>>> from tbldocuments join tblchanges using (changeID)
>>> group by 2;
>>> +--------+--------+
>>> | docIDs | YYYYMM |
>>> +--------+--------+
>>> | 1 | 200801 |
>>> | 1 1 | 200802 |
>>> | 2 1 4 | 200804 |
>>> | 2 | 200805 |
>>> | 3 4 | 200806 |
>>> +--------+--------+
>>> 5 rows in set (0.01 sec)
>>> Get the counts:
>>> mysql> select count(distinct docID),
>>> extract(year_month from datDate) as YYYYMM
>>> from tbldocuments join tblchanges using (changeID)
>>> group by 2;
>>> +-----------------------+--------+
>>> | count(distinct docID) | YYYYMM |
>>> +-----------------------+--------+
>>> | 1 | 200801 |
>>> | 1 | 200802 |
>>> | 3 | 200804 |
>>> | 1 | 200805 |
>>> | 2 | 200806 |
>>> +-----------------------+--------+
>>> 5 rows in set (0.00 sec)
>>> Here is a way to get the running total:
>>> mysql> set @t := 0;
>>> select @t := @t + months.n as running_total, months.*
>>> from ( select count(distinct docID) as n,
>>> extract(year_month from datDate) as ym
>>> from tbldocuments join tblchanges using (changeID)
>>> group by 2 ) as months
>>> order by ym;
>>> Query OK, 0 rows affected (0.00 sec)
>>> +---------------+---+--------+
>>> | running_total | n | ym |
>>> +---------------+---+--------+
>>> | 1 | 1 | 200801 |
>>> | 2 | 1 | 200802 |
>>> | 5 | 3 | 200804 |
>>> | 6 | 1 | 200805 |
>>> | 8 | 2 | 200806 |
>>> +---------------+---+--------+
>>> 5 rows in set (0.01 sec)
>> Toby,
>>
>> Thanks for responding. I'm not sure whether we're on the same page, so
>> I'll give some more information on the problem.
>>
>> Each month new documents are uploaded to the site. Here is my list of
>> document additions. As you can see, one change date, many documents:
>>
>> http://www.railwaysarchive.co.uk/updates.php
>>
>> To give an idea of the proposed use, here is the resulting graph at the
>> moment:
>>
>> http://www.railwaysarchive.co.uk/docsdataset.php
>>
>> This currently uses:
>>
>> SELECT COUNT(*) AS documents, DATE_FORMAT(tblchanges.datDate, '%b %Y')
>> AS month
>> FROM tblchanges
>> INNER JOIN tbldocuments ON tblchanges.changeID = tbldocuments.changeID
>> GROUP BY month
>> ORDER BY datDate ASC
>>
>> i.e. not cumulative - the cumulative part is done by maths within my php
>> code - i.e. $doctotal = $doctotal + $thismonthsdocs. I don't like this,
>> it's ugly and I would prefer mysql to handle the problem.
>
> I think there are enough clues in the thread for you to solve it -
> e.g. running total implemented by user variables.

Thanks Toby. I have done some reading and I think I understand what is
happening in your sql, and how I would run the two queries in php.
However, running the following in phpmyadmin to test the queries:

SET @totaldocs := 0;
SELECT @totaldocs := @totaldocs + months.doccount AS documents, months.*
FROM (
SELECT COUNT(DISTINCT tbldocuments.docID) AS doccount,
EXTRACT(year_month from tblchanges.datDate) AS ym
FROM tblchanges INNER JOIN tbldocuments
ON tblchanges.changeID = tbldocuments.changeID
GROUP BY ym
) AS months
ORDER BY ym;

returns results, but the documents column is NULL all the way down.
months.doccount is calculating fine. Could you clue me in to where I am
going wrong?

GazK wrote:
> toby wrote:
>> On Nov 30, 4:08 pm, GazK <inva...@invalid.invalid> wrote:
>>> toby wrote:
>>>> On Nov 29, 11:50 am, GazK <inva...@invalid.invalid> wrote:
>>>>> I have a pair of tables which track documents, and when they were
>>>>> added
>>>>> to the database.
>>>>> tblchanges contains dates when documents were added. tbldocuments
>>>>> contains document records. The primary key changeID is linked to the
>>>>> foreign key changeID in tbldocuments. So far so boring. What I am
>>>>> trying
>>>>> to achieve is a query that returns the cumulative total of documents
>>>>> added each month from the beginning of the records to the present.
>>>>> It is
>>>>> assumed that there are no blank months, i.e. there is at least one
>>>>> change row each month.
>>>>> tblchanges
>>>>> changeID
>>>>> datDate
>>>>> tbldocuments
>>>>> docID
>>>>> changeID
>>>>> Can someone give me a clue? I'm not looking for a fully worked
>>>>> solution,
>>>>> but a pointer would be enormously ful. I've got a gut feeling
>>>>> that a
>>>>> subquery is required, but my brain's not working today.
>>>> Do you mean documents *changed* each month as a cumulative total?
>>>> Here is one way to visualise your data:
>>>> mysql> select group_concat(docID separator ' ') as docIDs,
>>>> extract(year_month from datDate) as YYYYMM
>>>> from tbldocuments join tblchanges using (changeID)
>>>> group by 2;
>>>> +--------+--------+
>>>> | docIDs | YYYYMM |
>>>> +--------+--------+
>>>> | 1 | 200801 |
>>>> | 1 1 | 200802 |
>>>> | 2 1 4 | 200804 |
>>>> | 2 | 200805 |
>>>> | 3 4 | 200806 |
>>>> +--------+--------+
>>>> 5 rows in set (0.01 sec)
>>>> Get the counts:
>>>> mysql> select count(distinct docID),
>>>> extract(year_month from datDate) as YYYYMM
>>>> from tbldocuments join tblchanges using (changeID)
>>>> group by 2;
>>>> +-----------------------+--------+
>>>> | count(distinct docID) | YYYYMM |
>>>> +-----------------------+--------+
>>>> | 1 | 200801 |
>>>> | 1 | 200802 |
>>>> | 3 | 200804 |
>>>> | 1 | 200805 |
>>>> | 2 | 200806 |
>>>> +-----------------------+--------+
>>>> 5 rows in set (0.00 sec)
>>>> Here is a way to get the running total:
>>>> mysql> set @t := 0;
>>>> select @t := @t + months.n as running_total, months.*
>>>> from ( select count(distinct docID) as n,
>>>> extract(year_month from datDate) as ym
>>>> from tbldocuments join tblchanges using (changeID)
>>>> group by 2 ) as months
>>>> order by ym;
>>>> Query OK, 0 rows affected (0.00 sec)
>>>> +---------------+---+--------+
>>>> | running_total | n | ym |
>>>> +---------------+---+--------+
>>>> | 1 | 1 | 200801 |
>>>> | 2 | 1 | 200802 |
>>>> | 5 | 3 | 200804 |
>>>> | 6 | 1 | 200805 |
>>>> | 8 | 2 | 200806 |
>>>> +---------------+---+--------+
>>>> 5 rows in set (0.01 sec)
>>> Toby,
>>>
>>> Thanks for responding. I'm not sure whether we're on the same page, so
>>> I'll give some more information on the problem.
>>>
>>> Each month new documents are uploaded to the site. Here is my list of
>>> document additions. As you can see, one change date, many documents:
>>>
>>> http://www.railwaysarchive.co.uk/updates.php
>>>
>>> To give an idea of the proposed use, here is the resulting graph at the
>>> moment:
>>>
>>> http://www.railwaysarchive.co.uk/docsdataset.php
>>>
>>> This currently uses:
>>>
>>> SELECT COUNT(*) AS documents, DATE_FORMAT(tblchanges.datDate, '%b %Y')
>>> AS month
>>> FROM tblchanges
>>> INNER JOIN tbldocuments ON tblchanges.changeID = tbldocuments.changeID
>>> GROUP BY month
>>> ORDER BY datDate ASC
>>>
>>> i.e. not cumulative - the cumulative part is done by maths within my php
>>> code - i.e. $doctotal = $doctotal + $thismonthsdocs. I don't like this,
>>> it's ugly and I would prefer mysql to handle the problem.
>>
>> I think there are enough clues in the thread for you to solve it -
>> e.g. running total implemented by user variables.
>
> Thanks Toby. I have done some reading and I think I understand what is
> happening in your sql, and how I would run the two queries in php.
> However, running the following in phpmyadmin to test the queries:
>
> SET @totaldocs := 0;
> SELECT @totaldocs := @totaldocs + months.doccount AS documents, months.*
> FROM (
> SELECT COUNT(DISTINCT tbldocuments.docID) AS doccount,
> EXTRACT(year_month from tblchanges.datDate) AS ym
> FROM tblchanges INNER JOIN tbldocuments
> ON tblchanges.changeID = tbldocuments.changeID
> GROUP BY ym
> ) AS months
> ORDER BY ym;
>
> returns results, but the documents column is NULL all the way down.
> months.doccount is calculating fine. Could you clue me in to where I am
> going wrong?

Ignore my last message - I thought it might be a limitation of
phpmyadmin, so I leapt in and set up my php script. And it works like a
dream. Thanks very much! Final code is:

# set mysql variable for running document total
if(mysql_query("SET @totaldocs = 0")) {

# create sql for running total
$sql = "
SELECT @totaldocs := @totaldocs + months.doccount AS documents,
months.*
FROM (
SELECT COUNT(DISTINCT tbldocuments.docID) AS doccount,
DATE_FORMAT(tblchanges.datDate, '%b %Y') AS month, tblchanges.datDate
AS date
FROM tblchanges INNER JOIN tbldocuments
ON tblchanges.changeID = tbldocuments.changeID
GROUP BY month
) AS months
ORDER BY date;
";

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

while($row = mysql_fetch_array($result)) {
# loop through results and add data to arrays

$xaxis[] = $row[month];

$series->add_data_tip($row[documents], "archive contains " .
$row[documents] . " documents");

$docCount = $row[documents];

}

}