SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64) 
 Apr 11 2022 16:24:07 
 Copyright (C) 2019 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE orders(
   "order"       INTEGER  NOT NULL PRIMARY KEY 
  ,orderDate   DATE NOT NULL
  ,order_value INTEGER  NOT NULL
  ,commission  INTEGER  NOT NULL
  ,partner1    VARCHAR(1) NOT NULL
  ,partner2    VARCHAR(1) NOT NULL
  ,partner3    VARCHAR(1) NOT NULL
);
INSERT INTO orders("order",orderDate,order_value,commission,partner1,partner2,partner3) 
  VALUES (1,'2022-10-01',1000,50,'A','B','C');
INSERT INTO orders("order",orderDate,order_value,commission,partner1,partner2,partner3) 
  VALUES (2,'2022-10-01',800,40,'C','D','F');
INSERT INTO orders("order",orderDate,order_value,commission,partner1,partner2,partner3) 
  VALUES (3,'2022-10-02',2000,100,'B','A','K');
3 rows affected

select * from orders;


order
orderDate
order_value
commission
partner1
partner2
partner3


1
2022-10-01
1000
50
A
B
C

2
2022-10-01
800
40
C
D
F

3
2022-10-02
2000
100
B
A
K


-- How much commission is partner 1 receiving
select partner1 as partner, commission/3 as commis
   from orders
   where orderDate between '2022-10-01' and '2022-10-20'


partner
commis


A
16

C
13

B
33


-- use UNION ALL to add the commision for partner2 and partner3
-- and SUM it all up.
select 
   partner,
   sum(commis) as commission
from (
   select partner1 as partner, commission/3 as commis
   from orders
   where orderDate between '2022-10-01' and '2022-10-20'
   union all 
   select partner2, commission/3 as commis
   from orders
   where orderDate between '2022-10-01' and '2022-10-20'
   union all 
   select partner3, commission/3 as commis
   from orders 
   where orderDate between '2022-10-01' and '2022-10-20'
  )c
group by partner;


partner
commission


A
49

B
49

C
29

D
13

F
13

K
33

order	orderDate	order_value	commission	partner1	partner2	partner3
1	2022-10-01	1000	50	A	B	C
2	2022-10-01	800	40	C	D	F
3	2022-10-02	2000	100	B	A	K