SQL – get distinct values and its frequency count of occurring in a group

I have a table data as:

CREATE TABLE SERP (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
s_product_id INT,
search_product_result VARCHAR(255)
);


INSERT INTO SERP(s_product_id, search_product_result)
VALUES 
   (0, 'A'),
   (0, 'B'),
   (0, 'C'),
   (0, 'D'),
   (1, 'A'),
   (1, 'E'),
   (2, 'A'),
   (2, 'B'),
   (3, 'D'),
   (3, 'E'),
   (3, 'D');

The data set is as follows:

s_product_id    |    search_product_result
___________________________________________
0               |    A
0               |    B
0               |    C
0               |    D
-------------------------------------------
1               |    A
1               |    E
-------------------------------------------
2               |    A
2               |    B
-------------------------------------------
3               |    D
3               |    E
3               |    D

I need to list all distinct search_product_result values and count frequencies of these values occurring in s_product_id.

Required Output result-set:

DISTINCT_SEARCH_PRODUCT  |   s_product_id_frequency_count    
------------------------------------------------------------
A                        |   3
B                        |   2
C                        |   1
D                        |   2 [occurred twice in 3, but counted only once.]
E                        |   2

Here, A occurs in three s_product_id : 0, 1, 2, B in two : 0, 2, and so on.

D occurred twice in the same group 3, but is counted only once for that group.

I tried grouping by search_product_result, but this counts D twice in the same group.

select search_product_result, count(*) as Total from serp group by search_product_result

Output:

search_product_result  |  Total
------------------------------------
A                      |  3
B                      |  2
C                      |  1
D                      |  3  <---
B                      |  2

Answer

You can try below – use count(distinct s_product_id)

select search_product_result, count(distinct s_product_id) as Total 
from serp group by search_product_result