Query to combine duplicates-CodePudding

I have a table that has a table with titles and description and most titles have one description but some have two or more. if a title has more than one description I need to display "Duplicate" description next to the title instead of an actual description.

Titles
______________
ID  Title  Description   
-----------------------
1   Test   ABCD             
2   Test   FEGH         
3   Test2  AVWL             
4   Test3  KLMN             
5   Test3  ASDF

From the above data my query should return 3 records:

Test    Duplicate   
Test2   AVWL            
Test3   Duplicate

I tried using

SELECT Title, CASE WHEN COUNT(Description) > 1 THEN 'Duplicate' ELSE Description END Title_desc
FROM Titles
GROUP BY Title

But it would not work, erroring out saying Description is not a part of group by. If I add Description to Group by then the query does not remove dups. Is there a way to accomplish what I need without having too many subqueries?

CodePudding user response：

You can do:

select
  title,
  case when cnt = 1 then d else 'Duplicate' end as val
from (
  select title, count(*) as cnt, max(description) as d
  from t
  group by title
) x

Or, without a subquery:

select title,
  case when count(*) = 1 then max(description) 
       else 'Duplicate' end as val
from t
group by title