add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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),
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
-- is there any way output to become one per line ?
SELECT options.option_id,
COUNT(DISTINCT CASE WHEN filter_counter.option_id = options.option_id
THEN product_id
END) option_count
FROM filter_counter
CROSS JOIN ( SELECT DISTINCT option_id
FROM filter_counter ) options
JOIN ( SELECT DISTINCT product_id
FROM filter_counter
WHERE option_id IN (52) ) filter1 USING (product_id)
GROUP BY options.option_id;
option_id option_count
38 0
49 2
51 1
52 21