Is it possible to exclude columns from a group by clause? I have two columns (source & cause) that are a part of my select statement that have to be in the group by otherwise I get an error. My issue is that instead of one row for each Job sometimes I get another row that has a value in one of the columns in the group by. I understand why I get this other row, it's because it's in the group by clause but I'm wondering if there's anyway to fix this in the query? or should I try to fix it in php?
select concat(concat(v_job_header.job,'-'),v_job_header.suffix) as Job,v_job_header.part,v_job_header.qty_order,
sum(case when v_job_operations_wc.workcenter = '0750' then v_job_operations_wc.hours_actual end) as WaterJet,
sum(case when v_job_operations_wc.workcenter IN ('0705','0710','0715') then v_job_operations_wc.hours_actual end) as Laser,
sum(case when v_job_operations_wc.workcenter IN ('0600','0610','1006','0650','1315') then v_job_operations_wc.hours_actual end) as Prep,
sum(case when v_job_operations_wc.workcenter IN ('1310','0755') then v_job_operations_wc.hours_actual end) as Machining,
sum(case when v_job_operations_wc.workcenter IN ('1515','1000','1002','1003','0901','1270') then v_job_operations_wc.hours_actual end) as Fab,
sum(case when v_job_operations_wc.workcenter = '1100' then v_job_operations_wc.hours_actual end) as Paint,
sum(case when v_job_operations_wc.workcenter = '1000' then v_job_operations_wc.hours_actual end) as Belts,
sum(case when v_job_operations_wc.workcenter = '1001' then v_job_operations_wc.hours_actual end) as Electrical,
sum(case when v_job_operations_wc.workcenter = '1520' then v_job_operations_wc.hours_actual end) as Crating_Skids,
sum(case when v_job_operations_wc.workcenter IN ('1004','1005','1350','1201') then v_job_operations_wc.hours_actual end) as Final_Assy,
sum(case when v_job_operations_wc.workcenter = '4330' then v_job_operations_wc.hours_actual end) as Shipping,
sum(v_job_operations_wc.hours_estimated) as total_hours_estimated,
gab_source_cause_codes.source,gab_source_cause_codes.cause
from v_job_header
left join v_job_operations_wc on v_job_operations_wc.job = v_job_header.job and v_job_header.suffix = v_job_operations_wc.suffix
left join gab_source_cause_codes on gab_source_cause_codes.job = v_job_operations_wc.job and gab_source_cause_codes.suffix = v_job_operations_wc.suffix and gab_source_cause_codes.seq = v_job_operations_wc.seq
where v_job_header.product_line = '01' and v_job_header.date_closed < '2019-01-01' and v_job_operations_wc.LMO = 'L' and v_job_operations_wc.seq < '99000'
group by Job,v_job_header.part,v_job_header.qty_order,gab_source_cause_codes.source,gab_source_cause_codes.cause
| Job | Part |q|Waterjet|Laser|Prep|Machining| Fab |Paint|Belt|elec|crating|final_assy|shipping|total_hours_estimated|source|cause|
|----------|------|-|--------|-----|----|---------|-----|-----|----|----|-------|----------|--------|---------------------|------|-----|
|A06063-002|908135|1| 0.03| 0.78| 1.1| 0.02| | | | 0 | | | 6.6743| | |
|A06097-001|906969|1| | .41|3.05| 9.49| | | | 0 | | | 8.4 | | |
|A06097-001|906969|1| | | | |21.99| | | | | | | 12.3|Machinging|Part Missing|
| Job | Part |q|Waterjet|Laser|Prep|Machining| Fab |Paint|Belt|elec|crating|final_assy|shipping|total_hours_estimated|source|cause|
|----------|------|-|--------|-----|----|---------|-----|-----|----|----|-------|----------|--------|---------------------|------|-----|
|A06063-002|908135|1| 0.03| 0.78| 1.1| 0.02| | | | 0 | | | 6.6743| | |
|A06097-001|906969|1| | .41|3.05| 9.49|21.99| | | | 0| | | 20.7|Machinging|Part Missing|
I added the min() function to the source and cause columns. This allowed me to take them out of the group by clause which got rid of the empty row if there was a value for source and cause.
I was able to get my desired output like this:
select concat(concat(v_job_header.job,'-'),v_job_header.suffix) as Job,v_job_header.part,v_job_header.qty_order,sum(case when v_job_operations_wc.workcenter = '0750' then v_job_operations_wc.hours_actual end) as WaterJet,
sum(case when v_job_operations_wc.workcenter IN ('0705','0710','0715') then v_job_operations_wc.hours_actual end) as Laser,
sum(case when v_job_operations_wc.workcenter IN ('0600','0610','1006','0650','1315') then v_job_operations_wc.hours_actual end) as Prep,
sum(case when v_job_operations_wc.workcenter IN ('1310','0755') then v_job_operations_wc.hours_actual end) as Machining,
sum(case when v_job_operations_wc.workcenter IN ('1515','1000','1002','1003','0901','1270') then v_job_operations_wc.hours_actual end) as Fab,
sum(case when v_job_operations_wc.workcenter = '1100' then v_job_operations_wc.hours_actual end) as Paint,
sum(case when v_job_operations_wc.workcenter = '1000' then v_job_operations_wc.hours_actual end) as Belts,
sum(case when v_job_operations_wc.workcenter = '1001' then v_job_operations_wc.hours_actual end) as Electrical,
sum(case when v_job_operations_wc.workcenter = '1520' then v_job_operations_wc.hours_actual end) as Crating_Skids,
sum(case when v_job_operations_wc.workcenter IN ('1004','1005','1350','1201') then v_job_operations_wc.hours_actual end) as Final_Assy,
sum(case when v_job_operations_wc.workcenter = '4330' then v_job_operations_wc.hours_actual end) as Shipping,
sum(v_job_operations_wc.hours_estimated) as total_hours_estimated,
min(gab_source_cause_codes.source),min(gab_source_cause_codes.cause)
from v_job_header
left join v_job_operations_wc on v_job_operations_wc.job = v_job_header.job and v_job_header.suffix = v_job_operations_wc.suffix
left join gab_source_cause_codes on gab_source_cause_codes.job = v_job_operations_wc.job and gab_source_cause_codes.suffix = v_job_operations_wc.suffix and gab_source_cause_codes.seq = v_job_operations_wc.seq
where v_job_header.product_line = '01' and v_job_header.date_closed < '2019-01-01' and v_job_operations_wc.LMO = 'L' and v_job_operations_wc.seq < '99000'
group by Job,v_job_header.part,v_job_header.qty_order