How to query GROUP BY Month in a Year

Question

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

User · Answer

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 · Answer

You can use       select FK Items Sum PoiQuantity  Quantity  from PurchaseOrderItems POI     left join PurchaseOrder PO ON po ID PurchaseOrder poi FK PurchaseOrder     group by FK Items DATEPART MONTH  TransDate

User · Answer

For MS SQL you can do this       select  CAST DATEPART MONTH  DateTyme  as VARCHAR         CAST DATEPART YEAR  DateTyme  as VARCHAR  as  Date  from  temp     group by Name  CAST DATEPART MONTH  DateTyme  as VARCHAR         CAST DATEPART YEAR  DateTyme  as VARCHAR

User · Answer

I would be inclined to include the year in the output   One way   select to char DATE CREATED   YYYY-MM    sum Num of Pictures  from pictures table group by to char DATE CREATED   YYYY-MM   order by 1   Another way  more standard SQL    select extract year from date created  as yr  extract month from date created  as mon         sum Num of Pictures  from pictures table group by extract year from date created   extract month from date created  order by yr  mon    Remember the order by  since you presumably want these in order  and there is no guarantee about the order that rows are returned in after a group by

User · Answer

For Oracle   select EXTRACT month from DATE CREATED   sum Num of Pictures  from pictures table group by EXTRACT month from DATE CREATED

[sql] How to query GROUP BY Month in a Year

Examples related to sql

Examples related to oracle