Problem grouping by date - Informix SQL
Problem grouping by date - Informix SQL
(OP)
I hope someone can give me a hand while I'm learning the ins-and-outs of Informix. I have a table with a bunch of data including dates in Datetime Year to Minute format. I want to run a query and sum it up by date, I'm struggling with it's wonky time formats.
My data looks something similar to
So I'm expecting output like
Instead I'm getting this output
I've tried select distinct date(d_time) and several other ideas but I keep getting lots of rows as it's grouping by those individual times of the day and not the day itself.
I'm sorry but I do not know the exact version of Informix, I have ODBC access to the machine only.
CODE
select date(d_time), sum(somefield)
from my.table
where d_time > datetime(2008-12-01) year to day
group by 1
from my.table
where d_time > datetime(2008-12-01) year to day
group by 1
My data looks something similar to
CODE
2008-12-01 8:01 12
2008-12-01 12:15 7
2008-12-01 14:08 15
2008-12-02 10:50 10
2008-12-01 12:15 7
2008-12-01 14:08 15
2008-12-02 10:50 10
So I'm expecting output like
CODE
2008-12-01 34
2008-12-02 10
2008-12-02 10
Instead I'm getting this output
CODE
2008-12-01 12
2008-12-01 7
2008-12-01 15
2008-12-02 10
2008-12-01 7
2008-12-01 15
2008-12-02 10
I've tried select distinct date(d_time) and several other ideas but I keep getting lots of rows as it's grouping by those individual times of the day and not the day itself.
I'm sorry but I do not know the exact version of Informix, I have ODBC access to the machine only.
Talk To Other Members
RE: Problem grouping by date - Informix SQL
CODE
FROM yourTable
WHERE d_time > '2008-12-01 00:00'
GROUP BY 1
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: Problem grouping by date - Informix SQL