0

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
3

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.