Using Pervasive SQL. I have a query w/ results below. However, N00 and S00 are the same person. so, instead of a separate line I need the "actual" values from N00 and S00 to be totaled together in one row. I have been trying to come up with a sub query but I'm not sure how to tell it to add the two rows together.
select sum(amount) as Actual,salesman_number
from gcg_7095_bookings
where prod_line IN ('01','03') and salesman_number in ('G00','M00','N00','S00','O00','T00') and order_date >= '2023-01-01'
group by salesman_number
Results:
Actual | Salesman_Number |
---|---|
284315.71 | G00 |
223959.78 | M00 |
45517.92 | N00 |
373578.72 | O00 |
355029.00 | S00 |
76325.80 | T00 |
What I need:
Actual | Salesman_Number |
---|---|
284315.71 | G00 |
223959.78 | M00 |
373578.72 | O00 |
400546.92 | S00,G00 |
76325.80 | T00 |
I found a solution below. Gordon Linoff's answer. adding a case statement in the group by
select sum(amount) as acutal,(case when salesman_number IN ('N00','S00') then 'DG' else salesman_number end) as salesman_number
from gcg_7095_bookings
where prod_line IN ('01','03') and salesman_number in ('G00','M00','N00','S00','O00','T00') and order_date >= '2023-01-01'
group by (case when salesman_number in ('N00','S00') then 'DG' else salesman_number end)