CODEWITHSUNDEEP
sqlgroup-bygoogle-bigqueryexpression
Jennifer.Sam
Jennifer.Sam

Reputation: 17

BIG QUERY: SELECT list expression references examiner.first_name which is neither grouped nor aggregated at [2:8]

Can anyone provide advice on below:

select 
concat(examiner.first_name," ", examiner.last_name) as full_name, 
count(*) AS Rebuttals_Concluded, 
coalesce(r.FNL_APRVD_DT, r.FNL_DECLND_DT) AS fnl_rebuttal_date 

from `ltl-prd-datahub.DATAHUB_ALL_VIEWS.CLM_REBUTTAL_RDS_VW`r

join `ltl-prd-datahub.DATAHUB_ALL_VIEWS.CLM_CLAIM_RDS_VW`c
  on c.clm_id = r.clm_id 

join `ltl-prd-datahub.DATAHUB_ALL_VIEWS.EMPLOYEE_CURRENT_VW` examiner
  on c.EXAMINED_BY_EMPL_ID = examiner.EMPLOYEE_NUMBER 

where r.intrnl_stat_cd IN ('APPROVED','DECLINED')

Upvotes: 0

Views: 530

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You can use column aliases in BigQuery, so I would recommend:

select concat(examiner.first_name, ' ', examiner.last_name) as full_name, 
       count(*) AS Rebuttals_Concluded, 
       coalesce(r.FNL_APRVD_DT, r.FNL_DECLND_DT) AS fnl_rebuttal_date 
from `ltl-prd-datahub.DATAHUB_ALL_VIEWS.CLM_REBUTTAL_RDS_VW`r
join 
     `ltl-prd-datahub.DATAHUB_ALL_VIEWS.CLM_CLAIM_RDS_VW`c
      on c.clm_id = r.clm_id join
      `ltl-prd-datahub.DATAHUB_ALL_VIEWS.EMPLOYEE_CURRENT_VW` examiner
      on c.EXAMINED_BY_EMPL_ID = examiner.EMPLOYEE_NUMBER 
where r.intrnl_stat_cd IN ('APPROVED', 'DECLINED') 
group by full_name, fnl_rebuttal_date;

Upvotes: 1

GMB
GMB

Reputation: 222462

The error message is clear enough. Just add the relevant columns to the group by clause:

select 
    concat(examiner.first_name," ", examiner.last_name) as full_name, 
    count(*) AS Rebuttals_Concluded, 
    coalesce(r.FNL_APRVD_DT, r.FNL_DECLND_DT) AS fnl_rebuttal_date 
from `ltl-prd-datahub.DATAHUB_ALL_VIEWS.CLM_REBUTTAL_RDS_VW`r
join `ltl-prd-datahub.DATAHUB_ALL_VIEWS.CLM_CLAIM_RDS_VW`c
    on c.clm_id = r.clm_id 
join `ltl-prd-datahub.DATAHUB_ALL_VIEWS.EMPLOYEE_CURRENT_VW` examiner
    on c.EXAMINED_BY_EMPL_ID = examiner.EMPLOYEE_NUMBER 
where r.intrnl_stat_cd IN ('APPROVED','DECLINED') 
group by examiner.first_name, examiner.last_name, coalesce(r.FNL_APRVD_DT, r.FNL_DECLND_DT)

Upvotes: 1

Related Questions