CODEWITHSUNDEEP
sqloracle-database
Dwight Beech
Dwight Beech

Reputation: 21

Group by a portion of date (i.e. Year and Month) in Oracle SQL

I want to summarize a data value by area (defined as 501,502,503...) and by Year and Month. The year and month come from a date field in the database. So in effect I want to get the following, but I am getting totals for each area by day:

Area   Date      Value
501    10-2013   100000
501    11-2013   120000
502    10-2013   200000
502    11-2013   222000
...

Here is the SQL.

SELECT BUSINESS_ENTITY.BUSINESS_ENTITY_NAME, to_char(DATA_POINT_DETAIL.EFF_DATE,'MM-YYYY') as TONNAGE_DATE, sum(DATA_POINT_DETAIL.NUMERIC_VALUE) as ACTUAL

 FROM   OIS.BUSINESS_ENTITY BUSINESS_ENTITY INNER JOIN (((OIS.DATA_POINT_DETAIL DATA_POINT_DETAIL INNER JOIN OIS.DATA_POINT_HEADER DATA_POINT_HEADER ON DATA_POINT_DETAIL.DATA_POINT_ID=DATA_POINT_HEADER.DATA_POINT_ID) INNER JOIN OIS.CN_DATA_POINT_BUSINESS_ENTITY CN_DATA_POINT_BUSINESS_ENTITY ON DATA_POINT_HEADER.DATA_POINT_ID=CN_DATA_POINT_BUSINESS_ENTITY.DATA_POINT_ID) INNER JOIN OIS.BUSINESS_ENTITY_ANCESTOR BUSINESS_ENTITY_ANCESTOR ON (CN_DATA_POINT_BUSINESS_ENTITY.BUSINESS_ENTITY_ID=BUSINESS_ENTITY_ANCESTOR.BUSINESS_ENTITY_ID) AND (CN_DATA_POINT_BUSINESS_ENTITY.HIERARCHY_ID=BUSINESS_ENTITY_ANCESTOR.HIERARCHY_ID)) ON BUSINESS_ENTITY.BUSINESS_ENTITY_ID=BUSINESS_ENTITY_ANCESTOR.BUSINESS_ENTITY_ID

WHERE DATA_POINT_DETAIL.EFF_DATE>={?BeginDate} and  DATA_POINT_DETAIL.EFF_DATE<={?EndDate}
AND BUSINESS_ENTITY_ANCESTOR.BUSINESS_ENTITY_ANCESTOR_ID = 57570 
AND (DATA_POINT_HEADER.DATA_CATEGORY_NAME ='Tons Cut') and DATA_POINT_DETAIL.NUMERIC_VALUE<>0 and BUSINESS_ENTITY.BUSINESS_ENTITY_NAME<>'Ore'

group by BUSINESS_ENTITY.BUSINESS_ENTITY_NAME, DATA_POINT_DETAIL.EFF_DATE

I can't put a group by field for the field TONNAGE_DATE. I have tried many different variations and have failed.

This is run on an ORACLE Database from a Crystal Report.

Any help is appreciated,

Dwight

Upvotes: 2

Views: 27144

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Change your group by clause to:

group by BUSINESS_ENTITY.BUSINESS_ENTITY_NAME,
         to_char(DATA_POINT_DETAIL.EFF_DATE,'MM-YYYY');

Your query is grouping by the date (by day, presumably), and then formatting the output to have the month and year.

Upvotes: 7

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8113

You have to group by to_char(DATA_POINT_DETAIL.EFF_DATE,'MM-YYYY'), not just DATA_POINT_DETAIL.EFF_DATE, try this:

SELECT BUSINESS_ENTITY.BUSINESS_ENTITY_NAME, to_char(DATA_POINT_DETAIL.EFF_DATE,'MM-YYYY') as TONNAGE_DATE, sum(DATA_POINT_DETAIL.NUMERIC_VALUE) as ACTUAL

 FROM   OIS.BUSINESS_ENTITY BUSINESS_ENTITY INNER JOIN (((OIS.DATA_POINT_DETAIL DATA_POINT_DETAIL INNER JOIN OIS.DATA_POINT_HEADER DATA_POINT_HEADER ON DATA_POINT_DETAIL.DATA_POINT_ID=DATA_POINT_HEADER.DATA_POINT_ID) INNER JOIN OIS.CN_DATA_POINT_BUSINESS_ENTITY CN_DATA_POINT_BUSINESS_ENTITY ON DATA_POINT_HEADER.DATA_POINT_ID=CN_DATA_POINT_BUSINESS_ENTITY.DATA_POINT_ID) INNER JOIN OIS.BUSINESS_ENTITY_ANCESTOR BUSINESS_ENTITY_ANCESTOR ON (CN_DATA_POINT_BUSINESS_ENTITY.BUSINESS_ENTITY_ID=BUSINESS_ENTITY_ANCESTOR.BUSINESS_ENTITY_ID) AND (CN_DATA_POINT_BUSINESS_ENTITY.HIERARCHY_ID=BUSINESS_ENTITY_ANCESTOR.HIERARCHY_ID)) ON BUSINESS_ENTITY.BUSINESS_ENTITY_ID=BUSINESS_ENTITY_ANCESTOR.BUSINESS_ENTITY_ID

WHERE DATA_POINT_DETAIL.EFF_DATE>={?BeginDate} and  DATA_POINT_DETAIL.EFF_DATE<={?EndDate}
AND BUSINESS_ENTITY_ANCESTOR.BUSINESS_ENTITY_ANCESTOR_ID = 57570 
AND (DATA_POINT_HEADER.DATA_CATEGORY_NAME ='Tons Cut') and DATA_POINT_DETAIL.NUMERIC_VALUE<>0 and BUSINESS_ENTITY.BUSINESS_ENTITY_NAME<>'Ore'

group by BUSINESS_ENTITY.BUSINESS_ENTITY_NAME, to_char(DATA_POINT_DETAIL.EFF_DATE,'MM-YYYY')

Upvotes: 1

Related Questions