sql - 这个查询中如何使用sql count 求出每个人的职位控制号数？

Question

我正在尝试计算每个员工的职位控制号码的数量。我尝试使用 SELECT COUNT(hrp.pos_ctl_no)，但它只返回每个位置控制号的计数，而不是每个人的位置控制号的计数。在提供的示例结果中，Abbott Edith 有 2 个位置控制编号。我只希望她在电子表格中出现一次，她的名字计数为 2，以表明她有 2 个职位控制号码。相反，由于 2 个不同的控制编号，它向她展示了两次。

这是我的sql查询：

SELECT COUNT(hrp.pos_ctl_no),
empm.empl_no as "Employee Number",
hr.last_name as "Last Name", 
hr.first_name as "First Name",
isnull(hr.mid_name, '') as "Middle Name",
cctr.cctr_desc as "Work Location",
sj_desc as "Job Title",
code.st_job_cd as "Job Code",
hrp.pos_ctl_no as "Position Control Number"

FROM cenoff.hrperson hr
JOIN cenoff.prempm empm on empm.pers_id = hr.pers_id
JOIN [NGSQLSVR].[CENOFF].[PREMPJOB] job on job.empl_no = empm.empl_no
JOIN [NGSQLSVR].[CENOFF].[CCTR] cctr on cctr.glcval_val = job.glcval_val
JOIN [NGSQLSVR].[CENOFF].[PREMPCPI] CPI on cpi.empl_no = empm.empl_no
JOIN [NGSQLSVR].[CENOFF].[hrstpcode] code on code.st_job_cd = cpi.st_job_cd
JOIN [NGSQLSVR].[CENOFF].[pretype] on pretype.empl_type = job.empl_type
LEFT JOIN [NGSQLSVR].[CENOFF].[hrpcej] hrp on hrp.pers_id = hr.pers_id


WHERE 
job.job_type = 'P'
and cpi.assign_perc = '1.0000'
and job.job_no = '1'
--and hrp.pos_ctl_no is NULL
and empm.cpi_stat = 'A'  
--and cctr.cctr_desc = 'Isabella Complex'

GROUP BY 
empm.empl_no,
hrp.pos_ctl_no,
hr.last_name,
hr.first_name, 
isnull(hr.mid_name, ''), 
cctr.cctr_desc,
sj_desc,
code.st_job_cd, 
pretype.empl_type_desc, 
cpi.assign_perc, 
job.job_no, 
hr.hr_stat_cd, 
empm.cpi_stat, 
empm.hr_stat_cd,
job.hr_stat_cd, 
pos_began,
hrp.pos_ended, 
job.hire_date, 
enroll_end_date, 
contr_end_date 

ORDER BY hr.last_name, hr.first_name

结果

标签： sqlgroup-bycountsubqueryaggregate-functions

Answer 1

没有数据很难理解。从 group by 子句中删除pos_ctl_no可能会奏效。

SELECT COUNT(hrp.pos_ctl_no),
empm.empl_no as "Employee Number",
hr.last_name as "Last Name", 
hr.first_name as "First Name",
isnull(hr.mid_name, '') as "Middle Name",
cctr.cctr_desc as "Work Location",
sj_desc as "Job Title",
code.st_job_cd as "Job Code",


FROM cenoff.hrperson hr
JOIN cenoff.prempm empm on empm.pers_id = hr.pers_id
JOIN [NGSQLSVR].[CENOFF].[PREMPJOB] job on job.empl_no = empm.empl_no
JOIN [NGSQLSVR].[CENOFF].[CCTR] cctr on cctr.glcval_val = job.glcval_val
JOIN [NGSQLSVR].[CENOFF].[PREMPCPI] CPI on cpi.empl_no = empm.empl_no
JOIN [NGSQLSVR].[CENOFF].[hrstpcode] code on code.st_job_cd = cpi.st_job_cd
JOIN [NGSQLSVR].[CENOFF].[pretype] on pretype.empl_type = job.empl_type
LEFT JOIN [NGSQLSVR].[CENOFF].[hrpcej] hrp on hrp.pers_id = hr.pers_id


WHERE 
job.job_type = 'P'
and cpi.assign_perc = '1.0000'
and job.job_no = '1'
--and hrp.pos_ctl_no is NULL
and empm.cpi_stat = 'A'  
--and cctr.cctr_desc = 'Isabella Complex'

GROUP BY 
empm.empl_no,
hr.last_name,
hr.first_name, 
isnull(hr.mid_name, ''), 
cctr.cctr_desc,
sj_desc,
code.st_job_cd, 


ORDER BY hr.last_name, hr.first_name