using sql query with group by date

Question

I'm trying to write this query to retrieve No. of records grouped by date:

SELECT system_date,count (is_paid)
  FROM TPWEB.TP_CLIENT_TENDER_TRANS
  where system_date between '1-DEC-12' and '31-DEC-12'
  group by system_date

But, I got result without grouping such as:

01-DEC-12   1
01-DEC-12   1
01-DEC-12   1
01-DEC-12   1

what is wrong...

Solution 1

As is documented the DATE datatype is a datetime and thus stores the hour, minute and second in addition to the attributes you'd, more reasonably expect, in a datatype with this name. If you want to count over a day you need to remove the time portion of your date. It is the default behaviour of TRUNC(), so to do this trunc(<date>) is all you need.

It's worth noting two things at this point:

1. I'm assuming system_date is a column in your table and not a misunderstanding of SYSDATE

2. Your between clause is completely incorrect, dangerously so.

   By the way your dates have been represented it appears as though your NLS_DATE_FORMAT is DD-MON-YYYY (see another answer of mine for more details). This means that when you implicitly convert a date into a character it is converted in this format.

   You're not using either a datetime literal or an explicit conversion of the values you're comparing to, which means your date is being implicitly converted to a character. However, when you do the comparison you'll find that things aren't always as they seem. Character comparison is, normally, binary; this means that the 10^th of February is not between the 10^th January and the 10^th March; "March" is smaller than "January".

   Always explicitly convert dates and always use dates when doing date comparisons.

Putting all of this together your query becomes:

select trunc(system_date), count(is_paid)`
  from TPWEB.TP_CLIENT_TENDER_TRANS
 where system_date between date '2012-12-01' and date '2012-12-31'
 group by trunc(system_date)

Solution 2

Use trunc function, the DATE datatype holds the time with it, so using trunc we can truncate time. Below query works!

SELECT trunc(system_date),count (is_paid)
  FROM TPWEB.TP_CLIENT_TENDER_TRANS
  where system_date between '1-DEC-12' and '31-DEC-12'
  group by trunc(system_date);

Solution 3

DATE columns in Oracle contain precision up to milliseconds, not just the date. If you're only interested in the date itself, you should TRUNC it to remove hours, minutes, etc.:

SELECT   system_date,COUNT (is_paid)
FROM     TPWEB.TP_CLIENT_TENDER_TRANS
WHERE    system_date BETWEEN '1-DEC-12' AND '31-DEC-12'
GROUP BY TRUNC(system_date, 'DD-MON-YY')