CODEWITHSUNDEEP
oracle-database
acolls_badger
acolls_badger

Reputation: 463

Group by month in Oracle

I'm trying to group by month in oracle, but I'm getting an invalid identifier on the "YEAR" function, not sure why.

Here is my code:

SELECT CAST(MONTH(day_date) AS VARCHAR(2)) + '-' + CAST(YEAR(day_date) AS VARCHAR(4)) AS MY_DATE,
sum(cash_sales) as cash_sales, sum(unit_sales) as unit_sales
FROM NC_SALES_CAT_TL
GROUP BY CAST(MONTH(day_date) AS VARCHAR(2)) + '-' + CAST(YEAR(day_date) AS VARCHAR(4))

How can I accomplish the desired grouping by month?

Upvotes: 0

Views: 15324

Answers (1)

Sylvain Leroux
Sylvain Leroux

Reputation: 51990

Concatenation operator in Oracle is ||. Not +:

SQL> select 'a' +' b' from dual;
select 'a' +' b' from dual
       *
ERROR at line 1:
ORA-01722: invalid number

SQL> select 'a' || 'b' from dual;

'A
--
ab

In addition, YEAR is a MySQL function. In Oracle, you will use EXTRACT(YEAR FROM ....)

Finally, are you aware of the TO_CHAR function?

So you should rewrite your whole query as:

SELECT TO_CHAR(day_date, 'MM-YYYY') AS MY_DATE,
       sum(cash_sales) as cash_sales, sum(unit_sales) as unit_sales
FROM NC_SALES_CAT_TL
GROUP BY TO_CHAR(day_date, 'MM-YYYY')

Upvotes: 3

Related Questions