Selected non-aggregate values must be part of the associated group

Database
N/A

Selected non-aggregate values must be part of the associated group

I'm running into the above error when executing my query. The only items I've not added are the aggregate functions. I'm a newbie to SQL. Please help.

SELECT TO_CHAR(ACCTG_PERD_MTH_YR_DTE,'MON YYYY') AS rptg_mnth,
SUBSTR(LGCY_IORG_ID,1,2) AS GRP_NBR,
SUBSTR(LGCY_IORG_ID,3,2) AS BR_NBR,
brrstr.dsc,
--Asst manager names, still need to add code for more than one name
RTRIM(assntmgr.EMP_FNAM)||' '||RTRIM(assntmgr.EMP_MNAM)||' '||RTRIM(assntmgr.EMP_LNAM) assnt_mgr_fullnam,
--Total Sum
SUM(csmth.fin_incm_amt+csmth.EST_TOT_FIN_AMT+(csmth.EMRP_PRICE_AMT - csmth.EMRP_COST_AMT)+(csmth.AMO_CHRG_AMT-csmth.AMO_COST_AMT)) AS tot_all_amt,
--F&I
CASE WHEN csmth.fin_incm_amt <> 0 THEN SUM(csmth.sls_qty) ELSE 0 END AS fandi_SLS_QTY,
CASE WHEN fandi_SLS_QTY= 0 then 0 ELSE SUM(csmth.fin_incm_amt)/ SUM(fandi_sls_qty) END AS fandi_mth_average,
DENSE_RANK () OVER (ORDER BY CASE WHEN SUM (fandi_SLS_QTY)= 0 then 0 ELSE SUM(csmth.fin_incm_amt)/ SUM(csmth.sls_qty) END ) as fandi_mth_average_rnk,
--Finance
CASE WHEN csmth.FIN_IND = 1 THEN SUM(csmth.sls_qty) ELSE 0 END AS fin_sls_qty,
CASE WHEN csmth.FIN_IND = 1 THEN SUM( csmth.EST_TOT_FIN_AMT) ELSE 0 END AS tot_fin_amt,
CASE WHEN fin_sls_qty= 0 then 0 ELSE SUM(csmth.EST_TOT_FIN_AMT)/ SUM(fin_sls_qty) END AS fin_mth_average,
CASE WHEN tot_all_amt <> 0 THEN (tot_fin_amt/ tot_all_amt)*100 ELSE 0 END AS fin_pen,
DENSE_RANK () OVER (ORDER BY CASE WHEN SUM(csmth.sls_qty)= 0 then 0 ELSE SUM(csmth.EST_TOT_FIN_AMT)/ SUM(csmth.sls_qty) END ) as fin_rnk,
--EMRP
CASE WHEN csmth.EMRP_IND = 1 THEN SUM(csmth.sls_qty) ELSE 0 END AS emrp_sls_qty,
CASE WHEN csmth.EMRP_IND = 1 THEN SUM(csmth.EMRP_PRICE_AMT - csmth.EMRP_COST_AMT) ELSE 0 END AS tot_emrp_amt,
CASE WHEN emrp_sls_qty= 0 then 0 ELSE SUM(csmth.EMRP_PRICE_AMT - csmth.EMRP_COST_AMT)/ SUM(emrp_sls_qty) END AS emrp_mth_average,
CASE WHEN tot_all_amt<> 0 THEN (tot_emrp_amt/ tot_all_amt)*100 ELSE 0 END AS emrp_pen,
DENSE_RANK () OVER (ORDER BY CASE WHEN SUM(emrp_sls_qty)= 0 then 0 ELSE SUM(csmth.EMRP_PRICE_AMT - csmth.EMRP_COST_AMT) / SUM(emrp_sls_qty) END ) as emrp_rnk,
--AMO
CASE WHEN csmth.AMO_IND = 1 THEN SUM(csmth.sls_qty) ELSE 0 END AS amo_sls_qty,
CASE WHEN csmth.AMO_IND = 1 THEN SUM(csmth.AMO_CHRG_AMT-csmth.AMO_COST_AMT) ELSE 0 END AS tot_amo_amt,
CASE WHEN amo_sls_qty= 0 then 0 ELSE SUM(csmth.AMO_CHRG_AMT - csmth.AMO_COST_AMT)/ SUM(csmth.sls_qty) END AS amo_mth_average,
CASE WHEN tot_all_amt <>0 THEN (tot_amo_amt/ tot_all_amt)*100 ELSE 0 END AS amo_pen,
DENSE_RANK () OVER (ORDER BY CASE WHEN SUM(csmth.sls_qty)= 0 then 0 ELSE SUM(csmth.AMO_CHRG_AMT - csmth.AMO_COST_AMT) / SUM(csmth.sls_qty) END ) as amo_rnk,
--Total Rank
DENSE_RANK () OVER (ORDER BY fin_pen DESC ) + DENSE_RANK () OVER (ORDER BY emrp_pen DESC) + DENSE_RANK () OVER (ORDER BY amo_pen DESC) AS tot_rnk
--
FROM VEH_MGMT_LAB.CSM_CS_MSTR_MTH_SUM csmth
INNER JOIN LRD.LRD_IORG lrd ON
csmth.SLS_BR_IORG_ID = lrd.IORG_ID
LEFT OUTER JOIN VEH_MGMT_LAB.CSM_BR_RSTR brrstr ON
lrd.LGCY_IORG_ID = brrstr.LGCY_GRP_NBR||brrstr.LGCY_BR_NBR
--Asst Manager information
LEFT OUTER JOIN (
SELECT *
FROM
(
SELECT LGCY_EMP_ID,EMP_ID, BR_IORG_ID,EMP_FNAM, EMP_LNAM, EMP_MNAM,
RANK() OVER(PARTITION BY BR_IORG_ID ORDER BY EMP_ID DESC) as rnk
FROM VEH_MGMT_LAB.CSM_ERD_DLY_SNPSHT
WHERE CURR_VRSN_IND = 1
and assnt_mgr_ind = 1
and emp_stat in ('A','P')
) a
WHERE a.rnk = 1
) assntmgr ON
csmth.SLS_BR_IORG_ID = assntmgr.BR_IORG_ID
WHERE ACCTG_PERD_MTH_YR_DTE = TO_DATE('20160101','YYYYMMDD')
GROUP BY TO_CHAR(ACCTG_PERD_MTH_YR_DTE,'MON YYYY'),
SUBSTR(LGCY_IORG_ID,1,2),
SUBSTR(LGCY_IORG_ID,3,2),
brrstr.dsc,
assntmgr.EMP_FNAM,assntmgr.EMP_MNAM,assntmgr.EMP_LNAM
1 REPLY
Teradata Employee

Re: Selected non-aggregate values must be part of the associated group

Instead of

CASE WHEN condition THEN SUM(expression) ELSE 0 END

you need to code these as

SUM(CASE WHEN condition THEN expression ELSE 0 END)