PL/SQL EXTRACT(YEAR FROM MYDATE) not a GROUP BY expression

I have table MYTABLE, columns mydate and quantity, they have VARCHAR2 type.

|mydate|   |quantity|
10/15/2010     15
01/20/2010     20
05/16/2005     30
04/29/2005     50
03/30/2008     5

I want to get:

|year|   |quantity|
  2010         35
  2005         80
  2008          5

I try:

SELECT to_char(mydate,'yyyy') YEAR, SUM(to_number(quantity)) FROM MYTABLE GROUP BY to_char(mydate,'yyyy');

But i get error “ORA-00979: not a GROUP BY expression”.
What did i do wrong?


Source: sql

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.