Menu
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...
963
Group by in Date fieldHere we can apply group by command on our date field. Along with the group by command we can use count command to count total number of records in each day. We will discuss some SQL examples using some examples.
The SQL GROUP BY Statement The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
Both GROUP BY and ORDER BY are clauses (or statements) that serve similar functions; that is to sort query results. However, each of these serve very different purposes; so different in fact, that they can be employed separately or together.
GROUP BY Clause is utilized with the SELECT statement. GROUP BY aggregates the results on the basis of selected column: COUNT, MAX, MIN, SUM, AVG, etc. GROUP BY returns only one result per group of data. GROUP BY Clause always follows the WHERE Clause.
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:
I'm assuming system_date is a column in your table and not a misunderstanding of SYSDATE
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 10th of February is not between the 10th January and the 10th 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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
