AFJ
AFJ

Reputation: 159

Oracle SQL - repeating same column value within a group

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

order 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

Upvotes: 1

Views: 3190

Answers (1)

DCookie
DCookie

Reputation: 43523

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;

Upvotes: 3

Related Questions