BEGIN
Select codcomune,comune,codcategoria,sum(totstrutture),sum(totcamere),sum(totletti),sum(totbagni)From
(
SELECT 
a.codcomune,
com.comune as comune,
a.codcategoria,
count(a.codcomune) as totstrutture,
totcamere=(SELECT count(nrcamera)FROM dbo.anagrafica_camere Where codcomune=a.codcomune and codcategoria=a.codcategoria), 
totletti=(SELECT isnull(sum(nrletti)+sum(nrlettiextra),'0') FROM dbo.anagrafica_camere Where codcomune=a.codcomune and codcategoria=a.codcategoria),
totbagni=(SELECT isnull(Sum(nrbagni),'0') FROM dbo.anagrafica_camere Where codcomune=a.codcomune and codcategoria=a.codcategoria)

From Dbo.Albergo As a
JOIN
dbo.comuni as Com
ON a.codcomune=com.codcomune

GROUP BY
a.codcomune
,com.comune
,a.codcategoria

union all

SELECT 
d.codcomune,
com.comune as comune,
d.codcategoria,

count(d.codcomune) as totstrutture,
totcamere=(SELECT count(nrcamera)FROM dbo.anagrafica_camere Where codcomune=d.codcomune and codcategoria=d.codcategoria), 
totletti=(SELECT isnull(sum(nrletti)+sum(nrlettiextra),'0') FROM dbo.anagrafica_camere Where codcomune=d.codcomune and codcategoria=d.codcategoria),
totbagni=(SELECT isnull(Sum(nrbagni),'0') FROM dbo.anagrafica_camere Where codcomune=d.codcomune and codcategoria=d.codcategoria)

From Dbo.dependance As d
JOIN
dbo.comuni as Com
ON d.codcomune=com.codcomune

GROUP BY
d.codcomune
,com.comune
,d.codcategoria
)As Q
group by codcomune,comune,codcategoria