I want to get the count of p1 which r1 having

I want to get the result like bellow

Can anyone please help to write MySQL query to get this?

CodePudding user response：

Distinct can help to avoid duplicate values while counting.

distinct with count group by :

select R1, count(distinct P1) from test 
group by R1

CodePudding user response：

It does not produce duplicate using group by if the condition (table and query) are as below and it does not need distinct in this case:

DISTINCT can be considered as a special case of GROUP BY ( SELECT DISTINCT c1 from t1 is the same as SELECT c1 from t1 GROUP BY c1 ) and the same restrictions apply so GROUP BY clause of SELECT DISTINCT can only include expressions or columns already selected

CREATE TABLE `test` (
  r1 varchar(10),
  p1 varchar(10) );

INSERT INTO `test` VALUES
('RC1','PC1'),
('RC1','PC1'),
('RC2','PC2'),
('RC1','PC3'),
('RC1','PC3');

SELECT  r1,count(p1) 
FROM test
GROUP BY r1;

Demo: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/48