Counting total results from a table

[Alexandra]

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Counting total results from a table

[Arno Coetzee]

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 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]