时间戳

首页 > 解决方案 > 站点代码匹配的所有字段 A 的总和

sql - 站点代码匹配的所有字段 A 的总和

问题描述

我有一些现有的代码：

select
job.job_number,
job_status_log.status_code,
job_status.status_name,
central_site.site_code,
central_site.site_name,
'wc ' || to_char(trunc(job.job_entry_date, 'IW'), 'dd month')  as week_job_raised,
TO_CHAR(job.job_entry_date, 'MONTH') as month_job_raised,
'wc ' || to_char(trunc(job.actual_comp_date, 'IW'), 'dd month')  as week_job_complete,
TO_CHAR(job.actual_comp_date, 'MONTH') as month_job_complete,
job.job_entry_date as Job_Started,
job.actual_comp_date as Job_Completed,
job.job_notes,
job.job_easting,
job.job_northing,
job_attribute.param_num_value as Hours_On_Job

from
job
inner join job_status_log on job.job_number = job_status_log.job_number
inner join job_status on job_status_log.status_code = job_status.status_code
inner join central_site on job.site_code = central_site.site_code
inner join job_attribute on  job.job_number = job_attribute.job_number 

where
job_status_log.allocated_officer = 'ID51' and
job.job_log_number = job_status_log.job_log_number and
job_status_log.status_code not in ('XJOB','9990') and
job_attribute.param_type_code = 'GRFF' and
job.actual_comp_date is not null

产生以下内容：

Job Number    Site_Code     Hours_On_Job
12345            23               10
12346            23                8
12347            8                 2
12348            3                 3

我想做的是找到一种添加另一列的方法，以将报告中每个 SITE_CODE 的所有 HOURS_ON_JOB 值相加，然后将其显示在一个名为 TOTAL_HOURS_ON_SITE 的新列中

Job Number    Site_Code     Hours_On_Job     Total_Hours_On_Site
12345            23               10                 18
12346            23                8                 18 
12347            8                 2                  2 
12348            3                 3                  3

标签： sqloraclesum

解决方案

您可以添加列：

sum(job_attribute.param_num_value) over (partition by central_site.site_code) as Total_Hours_On_Site

这将为您提供按站点划分的作业的总和。根据您的样本数据，这可能是您想要的。

推荐阅读