Db2 Developer-C 11.1

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

CREATE TABLE Subjects
(
	id INT NOT NULL generated always as identity,
	subject VARCHAR(50),
	option1 smallint,
	option2 smallint,
	option3 smallint,
	PRIMARY KEY (ID)
);
 
CREATE TABLE Sales
(
	id INT NOT NULL generated always as identity,
	subject VARCHAR(50),
	sale int,
	dateSale DATE,
	PRIMARY KEY (ID)
);
INSERT INTO Subjects (subject, option1, option2, option3)
VALUES
('A', 1, 0, 0),
('B', 2, 0, 0),
('C', 0, 10, 0),
('D', 0, 20, 0),
('E', 0, 30, 0),
('F', 0, 40, 0),
('G', 0, 0, 100),
('H', 0, 0, 200),
('I', 0, 0, 300),
('J', 1, 10, 0),
('K', 1, 0, 100),
('L', 0, 10, 100);
INSERT INTO Sales (subject, sale, datesale)
VALUES
('A', 25, '2019-02-12'),
('A', 10, '2020-04-20'),
('C', 15, '2019-03-15'),
('C', 42, '2019-05-17'),
('C', 75, '2019-10-10'),
('D', 12, '2020-06-07'),
('E', 29, '2019-02-12'),
('F', 77, '2020-05-14'),
('F', 88, '2020-11-12'),
('K', 99, '2018-09-30');
select * from Subjects;


ID
SUBJECT
OPTION1
OPTION2
OPTION3


1
A
1
0
0

2
B
2
0
0

3
C
0
10
0

4
D
0
20
0

5
E
0
30
0

6
F
0
40
0

7
G
0
0
100

8
H
0
0
200

9
I
0
0
300

10
J
1
10
0

11
K
1
0
100

12
L
0
10
100


select * from Sales;


ID
SUBJECT
SALE
DATESALE


1
A
25
2019-02-12

2
A
10
2020-04-20

3
C
15
2019-03-15

4
C
42
2019-05-17

5
C
75
2019-10-10

6
D
12
2020-06-07

7
E
29
2019-02-12

8
F
77
2020-05-14

9
F
88
2020-11-12

10
K
99
2018-09-30


SELECT
s.opt,
s.saled,
COUNT(s.id) AS nbr_sub
 
FROM
(
	SELECT
	su.id,
	su.subject,
	CASE
		WHEN su.option1 <> 0 AND su.option2 = 0 AND su.option3 = 0 THEN 'option1'
		WHEN su.option1 = 0 AND su.option2 <> 0 AND su.option3 = 0 THEN 'option2'
		WHEN su.option1 = 0 AND su.option2 = 0 AND su.option3 <> 0 THEN 'option3'
		ELSE 'unknown'
	END AS opt,
	case when
	EXISTS
	(
		SELECT
		1
 
		FROM
		Sales AS sa
 
		WHERE
		sa.subject = su.subject
		AND YEAR(sa.dateSale) > 2019
	) 
	then true else false end
	AS saled
 
	FROM
	Subjects AS su
) AS s
 
GROUP BY
s.opt,
s.saled


OPT
SALED
NBR_SUB


option1
0
1

option1
1
1

option2
0
2

option2
1
2

option3
0
3

unknown
0
3

ID	SUBJECT	OPTION1	OPTION2	OPTION3
1	A	1	0	0
2	B	2	0	0
3	C	0	10	0
4	D	0	20	0
5	E	0	30	0
6	F	0	40	0
7	G	0	0	100
8	H	0	0	200
9	I	0	0	300
10	J	1	10	0
11	K	1	0	100
12	L	0	10	100

ID	SUBJECT	SALE	DATESALE
1	A	25	2019-02-12
2	A	10	2020-04-20
3	C	15	2019-03-15
4	C	42	2019-05-17
5	C	75	2019-10-10
6	D	12	2020-06-07
7	E	29	2019-02-12
8	F	77	2020-05-14
9	F	88	2020-11-12
10	K	99	2018-09-30

ID	SUBJECT	OPTION1	OPTION2	OPTION3
1	A	1	0	0
2	B	2	0	0
3	C	0	10	0
4	D	0	20	0
5	E	0	30	0
6	F	0	40	0
7	G	0	0	100
8	H	0	0	200
9	I	0	0	300
10	J	1	10	0
11	K	1	0	100
12	L	0	10	100

ID	SUBJECT	OPTION1	OPTION2	OPTION3
1	A	1	0	0
2	B	2	0	0
3	C	0	10	0
4	D	0	20	0
5	E	0	30	0
6	F	0	40	0
7	G	0	0	100
8	H	0	0	200
9	I	0	0	300
10	J	1	10	0
11	K	1	0	100
12	L	0	10	100