PostgreSQL查询报错：v.visit_id需出现在GROUP BY子句或聚合函数中求助

• 报错的直接诱因是你在SELECT开头写了DISTINCT ON (v.visit_id)：DISTINCT ON要求括号内指定的字段必须在GROUP BY子句中出现，或者被聚合函数包裹。你虽然在count(v.visit_id)里对该字段做了聚合，但DISTINCT ON是直接读取v.visit_id的原始行值，不属于聚合逻辑，因此触发规则报错。
• 从SQL执行顺序逻辑来看：GROUP BY的执行顺序早于SELECT子句的计算，当你仅按pt1.phys1_name分组时，分组后的结果集里不会保留单独的v.visit_id行值，DISTINCT ON自然无法读取到这个字段，就会触发报错。

根据你「按医生名字分组，同时统计去重的就诊ID数量」的需求，仅需要两处小修改即可，完全不需要把v.visit_id加入GROUP BY打乱输出逻辑：

1. 删掉开头多余的DISTINCT ON (v.visit_id)，这个语法在这里不需要，你的去重需求直接在聚合函数里实现即可。
2. 把count(v.visit_id) as total_visits改成count(DISTINCT v.visit_id) as total_visits，就能实现按医生分组后，统计每个医生对应的不重复就诊ID总数。

修改后的完整SQL如下：

select 
count(DISTINCT v.visit_id) as total_visits,
sum(mad2.nsma1_ans::time - mad.nsma1_ans::time) as or_hours_utilized,
sum(esla1_bt_end[1] - esla1_bt_beg[1]) as total_block_hours,
sum(extract(epoch from mad2.nsma1_ans::time) - extract(epoch from mad.nsma1_ans::time)) /
(sum(extract(epoch from esla1_bt_end[1])) - sum(extract(epoch from esla1_bt_beg[1]))) * 100 as or_percentage,
pt1.phys1_name as surgeon
from visit as v
inner join pat_phy_relation_table as pprt 
    on pprt.patphys_pat_num = v.visit_id
    and pprt.patphys_rel_type = 'ATTENDING' 
inner join physician_table1 as pt1
    on pt1.phys1_num = pprt.patphys_phy_num
    and pt1.phys1_arid = v.visit_arid --need to confirm how to handle ARIDs
inner join ews_location_table2 elt2
    on lpad(pt1.phys1_num::varchar, 6, '0') = any (elt2.esla1_bt_surg) 
    and esla1_loca in ('OR1','OR2','OR3','OR4') 
    and esla1_date between '2021-09-01' and '2021-09-30'
    and esla1_seid = pt1.phys1_arid
inner join multi_app_documentation mad2 
    on mad2.nsma1_patnum = v.visit_id
    and mad2.nsma1_code = 'OROUT' --only pulling visits/physicians with an OROUT    
    and mad2.nsma1_ans !~ '[x,X,C,END,S]' --removing non-standard data
    and mad2.nsma1_ans != '' and mad2.nsma1_ans != '0' and mad2.nsma1_ans != '1' and mad2.nsma1_ans != '0000'
inner join multi_app_documentation mad 
    on mad.nsma1_patnum = v.visit_id
    and mad.nsma1_code = 'ORINTIME' --only pulling visits/physicians with an ORINTIME
where v.visit_admit_date between '2021-09-01' and '2021-09-30'
and v.visit_arid = 5
group by pt1.phys1_name

内容的提问来源于stack exchange，提问作者BenSprad