Oracle GROUP BY question

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

Answer 1

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');

Answer 2

Try this:

SELECT to_char(EntryDate, 'mon') || to_char(EntryDate, 'yy), COUNT(OrderNo)
  FROM Orders
 GROUP BY to_char(EntryDate, 'mon') || to_char(EntryDate, 'yy);