SQL- How to aggreagte values by date? 1

[rodUecomm]

Hi,

Can some-one help me please.

I need to write a select statement that returns one row for each day of specified data range.
Simplified, I have 3 columns (two are integers, the other column is datetime) and the data will have many rows for each day of the month (entries are made at different times of the day). How do I aggregate one of the integer columns to show a total for each individual day for a given date range?

Rod

 

[jbcamel]

select sum(intfield), datefield from tablename
where datefield between "00/00/00" and "00/00/00"
group by datefield;

 

[rodUecomm]

HI,

thanks for replying. Unfortunately the result of that query returns a row for every date/time and I'm trying to get one row for each day. Any clues?

I tried using the 'Date' function but received
error 1218 - string to date conversion error.

here is the SQL I've tried (I have changed table and column names to make it easier to read):

select table1.id, table2.ddate, sum(table2.int1) sent,
sum(table2.int2) received
from table1, table2
where table1.id = 40 and table1.id = table2.id and
DATE (table2.ddate) between DATE ("2001-05-01&quot and
DATE ("2001-05-10&quot
group by table1.id, table2.ddate
order by table2.ddate;

Regards,
Rod

 

[jbcamel]

Rod,

Sorry - I didn't read your original post carefully enough and so I missed the fact that it was a datetime field.

I have a table. It has a datetime field and an integer field. The following query gave me a sum of the integer field by date:

select sum(intfield), date(datetimefield) from table
where date(datetimefield) between "01/01/2000" and "05/01/2001"
group by 2;


Jane