0

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?

Query

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

Sample data after running query

|   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|

Expected

|   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|
SkylarP
  • 39
  • 1
  • 8
  • You get 2 lines for `A06097-001` because you are also do a GROUP BY on `source` and `case`, and the values for those two rows are different. (Fixing would need to know the input data, and has nothing to do with PHP, or any other tool to query this data) – Luuk Jun 26 '22 at 13:12
  • Thank you for your comment @Luuk I understand why I am getting that additional row. I 'm wondering if it's possible to fix it. The only input will be v_job_header.product_line = '01'. It's the 2nd to last line in the query. There are about 12 product lines. I mention PHP b/c if it's not possible in the query I will have to use a PHP method on the array to combine those rows. – SkylarP Jun 27 '22 at 13:07
  • When you want to fix it, you should know what info should be shown in those 2 columns or, if you are not interested in the information from those columns, leave them out of your query, if you want the minimal value use `MIN(..)`, if you want to know the count of those values use `COUNT(...)`, etc. – Luuk Jun 27 '22 at 15:23
  • I'm confused by your comment. from the sample data above, we can see what data should be in those two columns under the expected header. I don't need the minimal value or the count or anything like that. I need the two rows combined if the Job column is the same. – SkylarP Jun 27 '22 at 15:36
  • I was talking about the columns `source` and `cause` For those columns more then 1 values exists (When Job='A06097-001') – Luuk Jun 27 '22 at 15:38

1 Answers1

0

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
SkylarP
  • 39
  • 1
  • 8