Friday, March 21, 2008

Use trunc in select query with group by clause - Oracle

Say you have a table `USER` as below

ID, JOIN_DATE, STATUS
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 do group by the SELECT query using the date field for each day then it would 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 would 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 same feature is also available in later version of mysql. But I don't know what is the exact function and way to use.

No comments:

Get function name programaticaly - Python

This little piece of code will help you to get the function name programatically. This is very helpful when you are implementing the debug...