Alexandra wrote:
> Hello,
>
> I am trying to build a sql statement for MySQL 4.0.x which does count
> the accumulated total entries in a table per day since a specified
> start date.
>
> For example:
> Day 1 = 10
> Day 2 = 12
> Day 3 = 9
>
> The statement I am using gives back the entries per day, starting
> each day with 0.
> For example:
> Day1 = 10
> Day2 = 2
> Day3 =0 (-3)
>
> code:
> SELECT DATE_FORMAT( timestamp, '%Y%m%d') AS mydate,
> count(*) AS ct,
> ID
> FROM $DB.$T4
> WHERE
> (timestamp >= '$date_start'
> AND
> timestamp <= '".$date_end."235959')
> AND
> confirmed = '1'
> GROUP BY
> mydate
>
> Has anybody an idea how to recraft the statement to get the
> accumulated entries per day?
>
> Thank you for any help,
>
> Alexandra
>
Hi Alexandra
you were not far off....
give this a go
SELECT DATE_FORMAT( timestamp, '%Y%m%d') AS mydate, count(*) AS ct
FROM $DB.$T4
WHERE timestamp >= '$date_start' AND timestamp <=
'".$date_end."235959') AND confirmed = '1'
GROUP BY DATE_FORMAT( timestamp, '%Y%m%d')
i don't know what the ID field in the select clause is for , so i
omitted it.
i suspect the ID field in the select clause caused the prob.
shout if you need more help.
--
Arno Coetzee
Developer
Flash Media Group
Office : +27 12 342 3490
Mobile : +27 82 693 6180
Fax : + 27 12 430 4269
www.flashmedia.co.za
<<FMG Total messaging solution. For all your GSM (SMS and USSD) messaging needs.
Quick and easy do-it-yourself interfaces. http://www.flashmedia.co.za/tms.php >>
