MySQL 8.0

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

CREATE TABLE `filter_counter` (
  `id` int(11) NOT NULL,
  `group_id` int(11) NOT NULL,
  `option_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `manufacturer_id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `filter_counter` (`id`, `group_id`, `option_id`, `product_id`, `category_id`, `manufacturer_id`) VALUES
(1, 33, 52, 5124, 65, 36),
(2, 33, 52, 5124, 127, 36),
(3, 33, 52, 5125, 65, 36),
(4, 33, 52, 5125, 127, 36),
(5, 33, 52, 5138, 65, 36),
(6, 33, 52, 5138, 127, 36),
(7, 33, 52, 5141, 65, 36),
(8, 33, 52, 5141, 127, 36),
(9, 33, 52, 5146, 65, 36),
(10, 33, 52, 5146, 127, 36),
(11, 33, 52, 5147, 65, 36),
(12, 33, 52, 5147, 127, 36),
(13, 33, 52, 5148, 65, 36),
(14, 33, 52, 5148, 127, 36),
(15, 33, 52, 5149, 65, 36),
(16, 33, 52, 5149, 127, 36),
(17, 33, 52, 5150, 65, 36),
(18, 33, 52, 5150, 127, 36),
(19, 33, 52, 5151, 65, 36),
(20, 33, 52, 5151, 127, 36),
(21, 33, 52, 5152, 65, 36),
(22, 33, 52, 5152, 127, 36),
(23, 33, 52, 5153, 65, 36),
(24, 33, 52, 5153, 127, 36),
(25, 33, 52, 5154, 65, 36),
(26, 33, 52, 5154, 127, 36),
(27, 33, 52, 5155, 65, 36),
(28, 33, 52, 5155, 127, 36),
(29, 33, 52, 5156, 65, 36),
(30, 33, 52, 5156, 127, 36),
(31, 33, 52, 5157, 65, 36),
(32, 33, 52, 5157, 127, 36),
(33, 33, 52, 7042, 65, 38),
(34, 33, 52, 7042, 127, 38),
(35, 33, 52, 7048, 65, 38),
(36, 33, 52, 7048, 127, 38),
(37, 33, 52, 7124, 65, 0),
(38, 33, 52, 7124, 127, 0),
(39, 32, 49, 7185, 65, 0),
(40, 32, 49, 7185, 127, 0),
(41, 32, 49, 7517, 65, 39),
(42, 32, 49, 7517, 127, 39),
(43, 32, 49, 7518, 65, 39),
(44, 32, 49, 7518, 127, 39),
(45, 32, 49, 7538, 65, 39),
(46, 32, 49, 7538, 127, 39),
(47, 32, 49, 7657, 65, 39),
(48, 32, 49, 7657, 127, 39),
(49, 32, 49, 7658, 65, 39),
(50, 32, 49, 7658, 127, 39),
(51, 32, 49, 7797, 65, 21),
(52, 32, 49, 7797, 127, 21),
(53, 32, 49, 7798, 65, 21),
(54, 32, 49, 7798, 127, 21),
(55, 32, 49, 7799, 65, 21),
(56, 32, 49, 7799, 127, 21),
(57, 32, 49, 7800, 65, 21),
(58, 32, 49, 7800, 127, 21),
(59, 32, 49, 7801, 65, 21),
(60, 32, 49, 7801, 127, 21),
(61, 32, 49, 7802, 65, 21),
(62, 32, 49, 7802, 127, 21),
(63, 32, 49, 7803, 65, 21),
(64, 32, 49, 7803, 127, 21),
(65, 32, 49, 7804, 65, 21),
(66, 32, 49, 7804, 127, 21),
(67, 32, 49, 7805, 65, 21),
(68, 32, 49, 7805, 127, 21),
(69, 32, 49, 7806, 65, 21),
(70, 32, 49, 7806, 127, 21),
(71, 32, 49, 7807, 65, 21),
(72, 32, 49, 7807, 127, 21),
(73, 32, 49, 7808, 65, 21),
(74, 32, 49, 7808, 127, 21),
(75, 32, 49, 7809, 65, 21),
(76, 32, 49, 7809, 127, 21),
(77, 32, 49, 7810, 65, 21),
(78, 32, 49, 7810, 127, 21),
(79, 29, 38, 7811, 65, 21),
(80, 29, 38, 7811, 127, 21),
(81, 32, 49, 8020, 65, 21),
(82, 32, 49, 8020, 127, 21),
(83, 33, 52, 8020, 65, 21),
(84, 33, 52, 8020, 127, 21),
(85, 32, 49, 8021, 65, 21),
(86, 32, 49, 8021, 127, 21),
(87, 33, 51, 8021, 65, 21),
(88, 33, 51, 8021, 127, 21),
(89, 33, 52, 8021, 65, 21),
(90, 33, 52, 8021, 127, 21);

SELECT * FROM filter_counter;

Records: 90  Duplicates: 0  Warnings: 0




id
group_id
option_id
product_id
category_id
manufacturer_id


1
33
52
5124
65
36

2
33
52
5124
127
36

3
33
52
5125
65
36

4
33
52
5125
127
36

5
33
52
5138
65
36

6
33
52
5138
127
36

7
33
52
5141
65
36

8
33
52
5141
127
36

9
33
52
5146
65
36

10
33
52
5146
127
36

11
33
52
5147
65
36

12
33
52
5147
127
36

13
33
52
5148
65
36

14
33
52
5148
127
36

15
33
52
5149
65
36

16
33
52
5149
127
36

17
33
52
5150
65
36

18
33
52
5150
127
36

19
33
52
5151
65
36

20
33
52
5151
127
36

21
33
52
5152
65
36

22
33
52
5152
127
36

23
33
52
5153
65
36

24
33
52
5153
127
36

25
33
52
5154
65
36

26
33
52
5154
127
36

27
33
52
5155
65
36

28
33
52
5155
127
36

29
33
52
5156
65
36

30
33
52
5156
127
36

31
33
52
5157
65
36

32
33
52
5157
127
36

33
33
52
7042
65
38

34
33
52
7042
127
38

35
33
52
7048
65
38

36
33
52
7048
127
38

37
33
52
7124
65
0

38
33
52
7124
127
0

39
32
49
7185
65
0

40
32
49
7185
127
0

41
32
49
7517
65
39

42
32
49
7517
127
39

43
32
49
7518
65
39

44
32
49
7518
127
39

45
32
49
7538
65
39

46
32
49
7538
127
39

47
32
49
7657
65
39

48
32
49
7657
127
39

49
32
49
7658
65
39

50
32
49
7658
127
39

51
32
49
7797
65
21

52
32
49
7797
127
21

53
32
49
7798
65
21

54
32
49
7798
127
21

55
32
49
7799
65
21

56
32
49
7799
127
21

57
32
49
7800
65
21

58
32
49
7800
127
21

59
32
49
7801
65
21

60
32
49
7801
127
21

61
32
49
7802
65
21

62
32
49
7802
127
21

63
32
49
7803
65
21

64
32
49
7803
127
21

65
32
49
7804
65
21

66
32
49
7804
127
21

67
32
49
7805
65
21

68
32
49
7805
127
21

69
32
49
7806
65
21

70
32
49
7806
127
21

71
32
49
7807
65
21

72
32
49
7807
127
21

73
32
49
7808
65
21

74
32
49
7808
127
21

75
32
49
7809
65
21

76
32
49
7809
127
21

77
32
49
7810
65
21

78
32
49
7810
127
21

79
29
38
7811
65
21

80
29
38
7811
127
21

81
32
49
8020
65
21

82
32
49
8020
127
21

83
33
52
8020
65
21

84
33
52
8020
127
21

85
32
49
8021
65
21

86
32
49
8021
127
21

87
33
51
8021
65
21

88
33
51
8021
127
21

89
33
52
8021
65
21

90
33
52
8021
127
21


/*
Size (group_id)
  10m (option_id: 52) (21 products)
  20m (option_id: 51) (1 product)

Color (group_id)
   Green (option_id: 49) (22 products)
   Black (option_id: 38) (1 product)
*/

SELECT COUNT(DISTINCT CASE WHEN option_id = 52 THEN product_id END) p52,
       COUNT(DISTINCT CASE WHEN option_id = 51 THEN product_id END) p51,
       COUNT(DISTINCT CASE WHEN option_id = 49 THEN product_id END) p49,
       COUNT(DISTINCT CASE WHEN option_id = 38 THEN product_id END) p38
FROM filter_counter;


p52
p51
p49
p38


21
1
22
1


/*
If a user select 10m (option_id: 52) the filter counter should become like this

Size (group_id)
  10m (option_id: 52) (21 products)
  20m (option_id: 51) (1)

Color (group_id)
   Green (option_id: 49) (2)
   Black (option_id: 38) (0)
*/
SELECT COUNT(DISTINCT CASE WHEN option_id = 52 THEN product_id END) p52,
       COUNT(DISTINCT CASE WHEN option_id = 51 THEN product_id END) p51,
       COUNT(DISTINCT CASE WHEN option_id = 49 THEN product_id END) p49,
       COUNT(DISTINCT CASE WHEN option_id = 38 THEN product_id END) p38
FROM filter_counter
JOIN ( SELECT DISTINCT product_id
       FROM filter_counter
       WHERE option_id IN (52) ) filter1 USING (product_id);


p52
p51
p49
p38


21
1
2
0

id	group_id	option_id	product_id	category_id	manufacturer_id
1	33	52	5124	65	36
2	33	52	5124	127	36
3	33	52	5125	65	36
4	33	52	5125	127	36
5	33	52	5138	65	36
6	33	52	5138	127	36
7	33	52	5141	65	36
8	33	52	5141	127	36
9	33	52	5146	65	36
10	33	52	5146	127	36
11	33	52	5147	65	36
12	33	52	5147	127	36
13	33	52	5148	65	36
14	33	52	5148	127	36
15	33	52	5149	65	36
16	33	52	5149	127	36
17	33	52	5150	65	36
18	33	52	5150	127	36
19	33	52	5151	65	36
20	33	52	5151	127	36
21	33	52	5152	65	36
22	33	52	5152	127	36
23	33	52	5153	65	36
24	33	52	5153	127	36
25	33	52	5154	65	36
26	33	52	5154	127	36
27	33	52	5155	65	36
28	33	52	5155	127	36
29	33	52	5156	65	36
30	33	52	5156	127	36
31	33	52	5157	65	36
32	33	52	5157	127	36
33	33	52	7042	65	38
34	33	52	7042	127	38
35	33	52	7048	65	38
36	33	52	7048	127	38
37	33	52	7124	65	0
38	33	52	7124	127	0
39	32	49	7185	65	0
40	32	49	7185	127	0
41	32	49	7517	65	39
42	32	49	7517	127	39
43	32	49	7518	65	39
44	32	49	7518	127	39
45	32	49	7538	65	39
46	32	49	7538	127	39
47	32	49	7657	65	39
48	32	49	7657	127	39
49	32	49	7658	65	39
50	32	49	7658	127	39
51	32	49	7797	65	21
52	32	49	7797	127	21
53	32	49	7798	65	21
54	32	49	7798	127	21
55	32	49	7799	65	21
56	32	49	7799	127	21
57	32	49	7800	65	21
58	32	49	7800	127	21
59	32	49	7801	65	21
60	32	49	7801	127	21
61	32	49	7802	65	21
62	32	49	7802	127	21
63	32	49	7803	65	21
64	32	49	7803	127	21
65	32	49	7804	65	21
66	32	49	7804	127	21
67	32	49	7805	65	21
68	32	49	7805	127	21
69	32	49	7806	65	21
70	32	49	7806	127	21
71	32	49	7807	65	21
72	32	49	7807	127	21
73	32	49	7808	65	21
74	32	49	7808	127	21
75	32	49	7809	65	21
76	32	49	7809	127	21
77	32	49	7810	65	21
78	32	49	7810	127	21
79	29	38	7811	65	21
80	29	38	7811	127	21
81	32	49	8020	65	21
82	32	49	8020	127	21
83	33	52	8020	65	21
84	33	52	8020	127	21
85	32	49	8021	65	21
86	32	49	8021	127	21
87	33	51	8021	65	21
88	33	51	8021	127	21
89	33	52	8021	65	21
90	33	52	8021	127	21