查询中出现GROUP BY问题

Question

首先，提前感谢任何人提供的帮助！

我是Oracle的新手，所以如果这个问题很愚蠢，我很抱歉。。。

我在正确运行查询时遇到问题，问题出在查询中的GROUP BY子句上。

当运行查询时，我得到一个错误，指出它不是GROUP BY语句？？不太清楚这意味着什么。

以下是查询：

with SUB_GRP_LVL_1 as
(
select sg.HCC_ID,sge.SUB_GROUP_NAME 
from XXXX_ODS.GROUP_T g
Join XXXX_ODS.SUB_GROUP sg
on g.GROUP_KEY=sg.GROUP_KEY
and g.HCC_ID NOT IN('A0002','A0003','A0004','A0005','A0021','A0022','A0041','A0042','A0081','A0121','A0061')
and sg.REC_SRC_CD='HS1'
and sg.LEVEL_NBR=1
join XXXX_ODS.SUB_GROUP_EFF sge
on sge.SUB_GROUP_KEY=sg.SUB_GROUP_KEY
---and sge.CURRENT_INDC=1
and sge.ACTIVE_INDC=1
)
,    
SUB_GRP_LVL_2 as
(
select sg.HCC_ID,sge.SUB_GROUP_NAME 
from XXXX_ODS.GROUP_T g
Join XXXX_ODS.SUB_GROUP sg
on g.GROUP_KEY=sg.GROUP_KEY
and g.HCC_ID NOT IN('A0002','A0003','A0004','A0005','A0021','A0022','A0041','A0042','A0081','A0121','A0061')
and sg.REC_SRC_CD='HS1'
and sg.LEVEL_NBR=2
join XXXX_ODS.SUB_GROUP_EFF sge
on sge.SUB_GROUP_KEY=sg.SUB_GROUP_KEY
and sge.ACTIVE_INDC=1
)
,
GRP_BEN_PL_ID as
(
select sg.SUB_GROUP_KEY,bpe.HCC_BENEFIT_PLAN_ID , sgbp.EFFECTIVE_DT, sgbp.EXPIRATION_DT, pe.FUNDING_TYPE, pe.HCC_PROD_ID, pe.STATE_CD, pe.SUB_COMPANY
from XXXX_ODS.GROUP_T g
Join XXXX_ODS.SUB_GROUP sg
on g.GROUP_KEY=sg.GROUP_KEY
and g.HCC_ID NOT IN('A0002','A0003','A0004','A0005','A0021','A0022','A0041','A0042','A0081','A0121','A0061')
and g.REC_SRC_CD='HS1'
JOIN XXXX_ODS.SUB_GROUP_BENEFIT_PLAN sgbp
on sg.SUB_GROUP_KEY=sgbp.SUB_GROUP_KEY
---and sgbp.CURRENT_INDC=1
and sgbp.ACTIVE_INDC=1
join XXXX_ODS.BENEFIT_PLAN_EFF bpe 
on sgbp.BENEFIT_PLAN_KEY = bpe.BENEFIT_PLAN_KEY
and bpe.ACTIVE_INDC=1
join XXXX_ods.product_eff pe
on bpe.PRODUCT_KEY=pe.product_key
and pe.ACTIVE_INDC=1
)
select 
BLK_1.SUB_GROUP_KEY,ACCT_NBR AS GROUPID,
SUB_GRP_ID, 
DIVISION_ID 
,GRP_BEN_PL_ID.HCC_BENEFIT_PLAN_ID
,INVOICE_NBR
,'CMD' BUSTYPE, 
GRP_BEN_PL_ID.FUNDING_TYPE AS BUSSUBTYPE,
GRP_BEN_PL_ID.HCC_PROD_ID AS PROCODE,
'' AS PREMIUMTYPE, 
INV.CURRENT_INVOICED_AMT AS PREMIUMBILLED, 
INV.CURRENT_INVOICED_AMT AS PREMIUMDUEDATE, 
'' AS PREMIUMPAID, 
'' AS PREMIUMAPPLYDATE, 
COUNT(invl.INVOICE_LINE_KEY) AS CONTRACTSXXXXL, 
'' AS MEMBERSXXXXL
--ACCOUNT_TYPE
,DECODE (UPPER(GRP_BEN_PL_ID.STATE_CD),'NEBRASKA','NE','IOWA','IA') STATE, 
GRP_BEN_PL_ID.SUB_COMPANY LGL_ENTITY,
LEVEL_NBR 
from
(
select 
g.GROUP_KEY, sg.SUB_GROUP_KEY, g.HCC_ID ACCT_NBR,ge.GROUP_NAME ACCT_NAME,substr(sg.HCC_ID, 1,instr(sg.hcc_id,'-',1,2)-1) SUB_GRP_ID,sg.HCC_ID DIVISION_ID,ce.FIRST_EFFECTIVE_DT,
add_months(
decode(ce.EFFECTIVE_DT,TO_DATE('01/01/1800','MM/DD/YYYY'),ce.FIRST_EFFECTIVE_DT,ce.EFFECTIVE_DT),ce.RENEWAL_INTERVAL) RENVL_DT, ce.ACCOUNT_TYPE
, sg.LEVEL_NBR
from 
XXXX_ODS.CUSTOMER_EFF ce
join XXXX_ODS.GROUP_T g
on g.CUSTOMER_KEY=ce.CUSTOMER_KEY
Join XXXX_ODS.SUB_GROUP sg
on g.GROUP_KEY=sg.GROUP_KEY
and g.HCC_ID NOT IN('A0002','A0003','A0004','A0005','A0021','A0022','A0041','A0042','A0081','A0121','A0061')
and g.REC_SRC_CD='HS1'
and sg.LEVEL_NBR=2
and g.HCC_ID not like 'TEST%'
join XXXX_ODS.GROUP_EFF ge
on ge.GROUP_KEY = g.GROUP_KEY
and ge.CURRENT_INDC=1
union  
select 
gp.GROUP_KEY,
sgp.SUB_GROUP_KEY, gp.HCC_ID ACCT,gep.GROUP_NAME, sgp.HCC_ID SUB_GRP, NULL DIVISION,cep.FIRST_EFFECTIVE_DT,
add_months(decode(cep.EFFECTIVE_DT,TO_DATE('01/01/1800','MM/DD/YYYY'),cep.FIRST_EFFECTIVE_DT,cep.EFFECTIVE_DT),cep.RENEWAL_INTERVAL) RENVL_DT,
cep.ACCOUNT_TYPE, sgp.LEVEL_NBR
from 
XXXX_ODS.CUSTOMER_EFF cep
join XXXX_ODS.GROUP_T gp
on gp.CUSTOMER_KEY=cep.CUSTOMER_KEY
Join XXXX_ODS.SUB_GROUP sgp
on gp.GROUP_KEY=sgp.GROUP_KEY
and gp.HCC_ID NOT IN('A0002','A0003','A0004','A0005','A0021','A0022','A0041','A0042','A0081','A0121','A0061')
and gp.REC_SRC_CD='HS1'
and sgp.LEVEL_NBR=1
and gp.HCC_ID not like 'TEST%'
join XXXX_ODS.GROUP_EFF gep
on gep.GROUP_KEY = gp.GROUP_KEY
and gep.CURRENT_INDC=1
and not exists
(
select 'X'
from 
XXXX_ODS.GROUP_T gi
Join XXXX_ODS.SUB_GROUP sgi
on gi.GROUP_KEY=sgi.GROUP_KEY
and gi.HCC_ID NOT IN('A0002','A0003','A0004','A0005','A0021','A0022','A0041','A0042','A0081','A0121','A0061')
and gi.REC_SRC_CD='HS1'
and sgi.LEVEL_NBR=2
and gi.HCC_ID not like 'TEST%'
where 
gp.HCC_ID=gi.HCC_ID 
and sgp.HCC_ID=substr(sgi.HCC_ID, 1,instr(sgi.hcc_id,'-',1,2)-1)
)
) BLK_1
join SUB_GRP_LVL_1 
on SUB_GRP_LVL_1.HCC_ID=BLK_1.SUB_GRP_ID
left join SUB_GRP_LVL_2 
on SUB_GRP_LVL_2.HCC_ID=BLK_1.DIVISION_ID
left join GRP_BEN_PL_ID
on GRP_BEN_PL_ID.SUB_GROUP_KEY=BLK_1.SUB_GROUP_KEY
LEFT Join XXXX_ODS.INVOICE inv
on inv.BILL_TO_GROUP_KEY = BLK_1.GROUP_KEY
LEFT JOIN XXXX_ODS.INVOICE_BILLING_CATEGORY invbc
on invbc.INVOICE_KEY = inv.INVOICE_KEY
LEFT JOIN XXXX_ODS.INVOICE_LINE invl
on invl.INVOICE_BILLING_CATEGORY_KEY = invbc.INVOICE_BILLING_CATEGORY_KEY
GROUP BY
GRP_BEN_PL_ID.SUB_GROUP_KEY,
ACCT_NBR, --AS GROUPID,
SUB_GRP_ID, 
DIVISION_ID 
,GRP_BEN_PL_ID.HCC_BENEFIT_PLAN_ID
,INVOICE_NBR
,BUSTYPE 
,GRP_BEN_PL_ID.FUNDING_TYPE,
,GRP_BEN_PL_ID.HCC_PROD_ID --AS PROCODE,
,PREMIUMTYPE
,INV.CURRENT_INVOICED_AMT 
PREMIUMBILLED, 
INV.CURRENT_INVOICED_AMT --AS PREMIUMDUEDATE, 
,PREMIUMPAID 
,PREMIUMAPPLYDATE 
,MEMBERSXXXXL
ACCOUNT_TYPE
,STATE_CD, 
GRP_BEN_PL_ID.SUB_COMPANY,
,LEVEL_NBR 
order by ACCT_NBR,SUB_GRP_ID,LEVEL_NBR,DIVISION_ID
;

谢谢！

Answer 1

基于评论的新答案

对不起，我应该仔细看一看。我读到了"我收到错误，说这不是GROUP BY语句"的语句，看到了计数，并用我原来的答案做出了假设。

根据评论中提供的进一步信息，我认为问题实际上是您在分组中使用了列别名。

分组时，您实际上必须使用字段名而不是别名，如果您有一个静态值(在本例中为"AS MEMBERXXXXL"(，则不需要按它进行分组SO

在组中使用别名选择错误

select column1, column2 || column 3 as test2, '' as test, count(1) as cnt
from table
group by column1, test2, test 

正确选择忽略静态值并通过将整个字段放入组中

select column1, column2 || column 3 as test2, '' as test, count(1) as cnt
from table
group by column1, column2 || column 3 

下面的原始答案如果您在select语句中有count、min、max、sum等，则需要告诉它如何对其他列进行分组。。。。

你有

COUNT(invl.INVOICE_LINE_KEY) AS CONTRACTSXXXXL,

这意味着您需要在选择中使用group-by子句。

是一个很好的基础资源

https://www.oracletutorial.com/oracle-basics/oracle-group-by/