Problem grouping by date - Informix SQL 1

Malekish · Dec 10, 2008

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.

Code:

select date(d_time), sum(somefield)
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

So I'm expecting output like

Code:

2008-12-01           34
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

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.

PHV · Dec 10, 2008

What about this ?

Code:

SELECT MDY(MONTH(d_time),DAY(d_time),YEAR(d_time)), SUM(somefield)
FROM yourTable
WHERE d_time > '2008-12-01 00:00'
GROUP BY 1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

Malekish · Dec 10, 2008

That did it! Thank you (and now to save that for future usage)

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Problem grouping by date - Informix SQL 1

Malekish

Technical User

PHV

MIS

Malekish

Technical User

Similar threads

Part and Inventory Search

Sponsor