CODEWITHSUNDEEP
sqloracle-database
C.Coggins
C.Coggins

Reputation: 215

Select month & year between specific range in Oracle

I have a column in my table called Notenum, which increments by 1 with each new insertion. It has about 2 million records for the past several years.

I want to find out how much (basically an average) the notenum column increases over a period of time. I don't necessarily need to get an average returned from the query. I'm fine with just getting a number and graphing it out, year by year or month by month.

Let's say I want to know how much it increase over the last 6 months, 2 years, etc...

Here is the query I tried to run, but I get the following error: ORA-00933: Sql command not properly ended. This is PL/SQL

SELECT TO_CHAR(systemts, 'mm') Month, TO_CHAR(systemts, 'yyyy') YEAR, notenum
FROM nsa_fl.polnote
WHERE systemts
    BETWEEN to_date('01/01/2013', 'mm-dd-yyyy')
    AND to_date('01/01/2014', 'mm-dd-yyyy')
GROUP BY TO_CHAR(systemts, 'mm') Month, TO_CHAR(systemts, 'yyyy') YEAR, notenum

I read through this How to group by each day in PL/SQL?, but I only got as far as this.

Thanks for the help.

Upvotes: 0

Views: 2964

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I think the value that you want is the difference between the minimum and maximum notenum in each time period:

SELECT TO_CHAR(systemts, 'yyyy-mm') as yyyymm,
       max(notenum) - min(notenum) + 1 as notenum_range
FROM nsa_fl.polnote
WHERE systemts >= to_date('2013-01-01', 'yyyy-mm-dd') AND
      systemts < to_date('2014-01-01', 'yyyy-mm-dd')
GROUP BY TO_CHAR(systemts, 'yyyy-mm');

I also made the following changes:

  • I combined the year and month into a single column.
  • I changed the between to two comparisons. This ensures that you get no data from 2014-01-01.
  • I modified the date format to be yyyy-mm-dd. It is a good habit to use the ISO standard format (in my opinion).
  • I removed the extraneous notenum from the group by clause.

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

There are two problems with your query. First, you have a group by clause without an aggregate like sum() or count(). The one causing the error message is that the group by clause has aliases.

Upvotes: 0

Related Questions