How to prevent duplication on the second table Using inner Joins and the Count-CodePudding

SELECT T.column_11, 
       count(column_11) count 
FROM wp_tablesome_table_4695 T 
JOIN wp_fea_submissions S ON T.column_2 = S.title 
group BY T.column_11

wp_tablesome_table_4695
column 2 | column 11
1        | location 1
2        | location 2
3        | Location 3

wp_fea_submissions
title 
1
1        
2        
3

Result

column 11| count
1        | 2
2        | 1
3        | 1

The count result is also counting the duplicate S.Title how to prevent counting the Duplicate

CodePudding user response：

The count need to be done inside a subquery.

SELECT T.column11, 
       count(column11) count 
FROM wp_tablesome_table_4695 T 
INNER JOIN  (select distinct title 
             from wp_fea_submissions
            )  S ON T.column2 = S.title 
group BY T.column11;

with cte as ( select distinct title
              from wp_fea_submissions
) select wp.column11, count(wp.column11) as cnt
  from cte 
  inner join wp_tablesome_table_4695 wp on wp.column2=cte.title
  group by wp.column11;

https://dbfiddle.uk/iVprcr8z