Home » SQL & PL/SQL » SQL & PL/SQL » GROUP BY COST CENTER
GROUP BY COST CENTER [message #659215] Mon, 09 January 2017 11:34 Go to next message
vharish006
Messages: 45
Registered: August 2015
Location: Chicago
Member
How do i include GROUP BY in below .I want to GROUP BY COST CENTER.

SELECT t."Fund" ,
  t."Cost Center" as "CostCenter",
  DECODE(t."Cost Center" ,'8620',DECODE(t."Location",'0001','8620',t."Location") ,t."Location" ) "Location",
  t."Account" ,
  t."Program" ,
  t."Categories" ,
  t."Session" ,
  t."Project",
  DECODE(SUBSTR(t."Account",1,1) ,'6', t."Data"  ,NULL ) as "Debit",
  DECODE(SUBSTR(t."Account",1,1) ,'4', t."Data" ,'3', t."Data",NULL ) as "Credit",
  ('JAN-'||SUBSTR(t.YEAR,-2)) as "Period",
  ('20'||SUBSTR(t.YEAR,-2)||' Upload') as "Line Description"
FROM
 (SELECT SUBSTR(FUND,                     -3) "Fund" ,
    SUBSTR(ENTITY,                         -4) "Cost Center" ,
    DECODE(SUBSTR(NVL(LOCATION,'LOC_0001'),-4) ,'0001',NVL(
      (SELECT b.flex_value segment_value
    FROM fnd_flex_value_sets a,
      fnd_flex_values_vl b
    WHERE a.flex_value_set_id = b.flex_value_set_id
    AND b.enabled_flag        = 'Y'
    AND a.flex_value_set_name = 'FAMIS_Location'
    AND b.flex_value          =SUBSTR(stg.ENTITY,-4)
    AND ROWNUM                =1
    ) ,'0001') ,SUBSTR(NVL(LOCATION,'LOC_0001'),-4) ) "Location" ,
    ACCOUNT "Account" ,
    '00001' "Program" ,
    '01' "Categories" ,
    SUBSTR(GL_SESSION,-2) "Session" ,
    '00001' "Project" ,
    UPPER(Period) "Period" ,
    BUDGET "Data" ,
     DECODE(UPPER(Period),'JAN',1,'FEB',2,'MAR',3,'APR',4,'MAY',5,'JUN',6,'JUL',7,'AUG',8,'SEP',9,'OCT',10,'NOV',11,'DEC',12,'YEARTOTAL',0 ,13) AS "MON_NUM" ,
    SCENARIO,
    VERSION,
    YEAR
  FROM CPD_GL_FROM_HYP_ADBF_STG stg--CPD_GL_FROM_HYP_CSCV_STG stg
  WHERE 1          =1
  AND UPPER(Period)='YEARTOTAL'
  ORDER BY 1,2,3,4,5,6,7,8,11
  )t
WHERE 1=1

--moderator update: I've added [code] tags, please do so yourself in future.

[Updated on: Mon, 09 January 2017 11:39] by Moderator

Report message to a moderator

Re: GROUP BY COST CENTER [message #659216 is a reply to message #659215] Mon, 09 January 2017 11:41 Go to previous messageGo to next message
John Watson
Messages: 7618
Registered: January 2010
Location: Global Village
Senior Member
Are you familiar with using aggregations? The rule is simple: every column you project must either be aggregated (with, for example, MAX or COUNT) or be included in the GROUP BY clause.
Re: GROUP BY COST CENTER [message #659217 is a reply to message #659215] Mon, 09 January 2017 11:50 Go to previous message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From your previous topics:

BlackSwan wrote on Mon, 10 August 2015 19:12
Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
...
Barbara formatted your post in your next topic and you still continued to post not formatted.

So once more:
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Also always post your Oracle version, with 4 decimals, as solution depends on it.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.



[Updated on: Mon, 09 January 2017 11:51]

Report message to a moderator

Previous Topic: check format
Next Topic: separate 2 columns into 2 rows
Goto Forum:
  


Current Time: Thu Oct 18 17:34:47 CDT 2018