renaming result of query

Question

i have two joined table in my postgres db

tb_items and tb_colors

id_item | group |id colors
--------------------------
1        a         1 
2        b         1
3        b         2
4        c         4
5        c         5

id_color | color| subcolor
---------------------------------
1          black        var1  
2          red          type1
3          red          type2
4          blue         aaa1
5          blue         xxx3
6          red          hex1 

that mean in my query i have

id_item | group | color | subcolor
----------------------------------
1         a       black    var1
2         b       red      type1
3         b       red      type2
4         c       blue     aa1
5         c       red      hex1 

basically what i want is renaming color name based on numers of color item in a groups or

 id_item | group | color 
----------------------------------
 1         a       black
 2         b       red
 3         b       red-1
 4         c       blue
 5         c       red

i dont' really know whato to do..

Answer 1

WITH 
cte1 AS ( SELECT t1.id_item, 
                 t1."group",
                 t2.id_color,
                 t2.color,
                 t2.subcolor
          FROM tb_items t1
          NATURAL JOIN tb_colors t2 ),
cte2 AS ( SELECT color, COUNT(DISTINCT subcolor) subcolors
          FROM cte1
          GROUP BY color )
SELECT cte1.id_item, 
       cte1."group",
       CASE cte2.subcolors 
       WHEN 1 THEN cte1.color
              ELSE cte1.color || '-' || ROW_NUMBER() OVER (PARTITION BY cte1.color
                                                           ORDER BY tb_colors.subcolor)
       END color
FROM tb_colors
NATURAL JOIN cte1
NATURAL JOIN cte2
ORDER BY cte1.id_item;

fiddle

Answer 2

A slight variation of Akinas solution:

select id_item
     , color || case when max(rn) over (partition by "group") = 1 then '' else '-' || rn end
from (
    select a.id_item, a."group", b.color
         , row_number() over (partition by a.group order by b.id_color) as rn
    from tb_items a
    natural join tb_colors b
) as t

His solution is a bit easier to grasp, so stick with that. Just added this as a alternative way of doing it.