select codcomune, comune, 
camere.codiceordinamento, strutture, camere, letti, bagni  from comuni
left outer join 
  (
  select codicecomune, codiceordinamento, max(strutture) as strutture, max(camere) as camere, max(letti) as letti, max(bagni) as bagni from 
    (  
    select  codicecomune, 
        case 
          when SUM(nrcamera)<=25 then 1
          when SUM(nrcamera)<100 then 2
          else 3
        end as codiceordinamento,
        count(codalbergo) as strutture, 
        ISNULL(SUM(nrcamera),0) as camere,
        ISNULL(SUM(nrletti),0)+ISNULL(SUM(nrlettiextra),0) as letti, 
        ISNULL(SUM(nrbagni),0) as bagni 
        FROM anagrafica_camere GROUP BY codicecomune
    ) as y group by codicecomune, codiceordinamento
  ) as camere
on comuni.codcomune=camere.codicecomune