Oracle GROUP BY question I am using Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production. Yes an ancient version but the license is embedded in an application that we use and this is the Oracle version that they use.

Here is an SQL query that I am using:

SELECT     EntryDate, COUNT(OrderNo)
FROM       Orders
GROUP BY   EntryDate;


I get results like:

EntryDate COUNT(OrderNo)
--------- --------------
01-NOV-12           543
02-NOV-12           555
03-NOV-12            91


There are currently 472 rows with dates up to today, so the above is only a small snapshot of results. 

Is it possible to group the results by month instead of the current date i.e. get results like:

EntryDate COUNT(OrderNo)
--------- --------------
Nov12     X
Dec12     X
Jan14     X
Round to the beginning of the month:

SELECT     trunc(EntryDate,'MM'), COUNT(OrderNo)
FROM       Orders
GROUP BY   trunc(EntryDate,'MM');


In oracle 8i the results are sorted because of the internal algorithm used by the database to do the 'GROUP BY'. In higher Oracle version beginning with 10g a different algorithm is used and the results will not be sorted. So if you want a sorted result you should tell it the database by querying

SELECT     trunc(EntryDate,'MM'), COUNT(OrderNo)
FROM       Orders
GROUP BY   trunc(EntryDate,'MM')
ORDER by   trunc(EntryDate,'MM');


even if the result will be  sorted by accident.

To get exactly the output of your query execute the following statement:

SELECT     to_char(trunc(EntryDate,'MM'),'MonYY'), COUNT(OrderNo)
FROM       Orders
GROUP BY   trunc(EntryDate,'MM')
ORDER by   trunc(EntryDate,'MM');