How to query GROUP BY Month in a Year

user_1500075 0
Viewed 240251

I am using Oracle SQL Developer. I essentially have a table of pictures that holds the columns:

[DATE_CREATED(date), NUM_of_PICTURES(int)]

and if I do a select *, I would get an output similar to:

01-May-12    12
02-May-12    15
03-May-12    09
...
...
01-Jun-12    20
...
etc.

I am trying to aggregate these sums of pictures into MONTHLY numbers instead of DAILY.

I've tried doing something like:

select Month(DATE_CREATED), sum(Num_of_Pictures))
from pictures_table
group by Month(DATE_CREATED);

This outputs an error:

ORA-00904: "MONTH": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 5 Column: 9

Do I have the Month function wrong?

oracle sql
5 Answers

I am doing like this in MSSQL

Getting Monthly Data:

 SELECT YEAR(DATE_CREATED) [Year], MONTH(DATE_CREATED) [Month], 
     DATENAME(MONTH,DATE_CREATED) [Month Name], SUM(Num_of_Pictures) [Pictures Count]
    FROM pictures_table
    GROUP BY YEAR(DATE_CREATED), MONTH(DATE_CREATED), 
     DATENAME(MONTH, DATE_CREATED)
    ORDER BY 1,2

Getting Monthly Data using PIVOT:

SELECT *
FROM (SELECT YEAR(DATE_CREATED) [Year], 
       DATENAME(MONTH, DATE_CREATED) [Month], 
       SUM(Num_of_Pictures) [Pictures Count]
      FROM pictures_table
      GROUP BY YEAR(DATE_CREATED), 
      DATENAME(MONTH, DATE_CREATED)) AS MontlySalesData
PIVOT( SUM([Pictures Count])   
    FOR Month IN ([January],[February],[March],[April],[May],
    [June],[July],[August],[September],[October],[November],
    [December])) AS MNamePivot
user_32644060
Related