I'd like some help tweaking the following query
select
data.smalldate,
mip.mip_step_description,
error_code.error_code_en,
count(case when (error_code is null and quality_plan is null) then data.part_serial_number end) as "Input",
count(case when error_code is not null then data.part_serial_number end) as "Defects"
from Data
left join MIP
On data.equipment = mip.equipment
left join error_code
on data.error_code = error_code.error_code_sn
group by data.smalldate, mip.mip_step_description, error_code.error_code_en
开发者_如何学JAVAorder by data.smalldate, mip.mip_step_description, count(data.part_serial_number) desc
As you can see in the select statement, I'm using case statements within my count functions. This works fine. Data output looks like this
Date MIP_Desc Error_Code Input Defects
1/1/2011 MIP Z (null) 100 0
1/1/2011 MIP Z A 0 10
1/1/2011 MIP Z B 0 15
I'd like to fill in the same input value in the input column throughout all the rows that have the same date and MIP_Desc.
Output should look like this
Date MIP_Desc Error_Code Input Defects
1/1/2011 MIP Z (null) 100 0
1/1/2011 MIP Z A 100 10
1/1/2011 MIP Z B 100 15
Does this help? (untested):
SELECT smalldate, mip_step_description
, error_code_en
, MAX("Input") OVER (PARTITION BY smalldate, mip_step_description) "Input"
, "Defects"
FROM (select data.smalldate, mip.mip_step_description, error_code.error_code_en
, count(case when (error_code is null and quality_plan is null)
then data.part_serial_number end) as "Input"
, count(case when error_code is not null
then data.part_serial_number end) as "Defects"
, count(data.part_serial_number) sn_ct
from DATA left join MIP On data.equipment = mip.equipment
left join ERROR_CODE on data.error_code = error_code.error_code_sn
group by data.smalldate, mip.mip_step_description, error_code.error_code_en)
order by smalldate, mip_step_description, sn_ct desc;
精彩评论