Friday, March 21, 2008

Oracle : use trunc at select query with group by clause

Share at Facebook

Say you have a table `USER`as below

00,'13-Sep-2006 08:47:54 AM', 0
01,'13-Sep-2006 08:48:54 AM', 0
02,'13-Sep-2006 08:49:54 AM', 0
03,'13-Sep-2006 08:50:54 AM', 0
04,'14-Sep-2006 08:54:54 AM', 0
10,'14-Sep-2006 08:59:54 AM', 0
12,'14-Sep-2006 09:00:54 AM', 0

If you want to group by the SELECT query using the date field for each day, it will consider the time with the date while grouping. For example the below query.

select JOIN_DATE, count(*) from USER where STATUS = 0 group by JOIN_DATE;

Output will be

13-Sep-2006 08:47:54 AM, 1
13-Sep-2006 08:48:54 AM, 1
13-Sep-2006 08:49:54 AM, 1
13-Sep-2006 08:50:54 AM, 1
14-Sep-2006 08:54:54 AM, 1
14-Sep-2006 08:59:54 AM, 1
14-Sep-2006 09:00:54 AM, 1

To get the exact result for each day by eliminating the time, you need to truncate the time from the date field. You can use the trunc function of oracle to remove time part.

select trunc(JOIN_DATE), count(*) from USER where STATUS = 0 group by trunc(JOIN_DATE);

Output will be

13-Sep-2006, 4
14-Sep-2006, 3

This kind of feature is also available at later version of mysql. But I don't know what is the exact function and way to use. If anyone know, can post here.

No comments: