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

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

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

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

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

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

Examples related to sql

Examples related to oracle