pervasivepervasive-sql

How to sum two calculated rows together


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

Solution

  • I found a solution below. Gordon Linoff's answer. adding a case statement in the group by

    Merging rows in SQL

    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)