Oracle GROUP BY question

oracle

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

Best Answer

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