declare @kat VarChar(100), @kp int, @sumv decimal(10,2), @srv decimal(10,5), @srv1 decimal(10,5)
--Итоговая таблица результата
CREATE TABLE #rez(kat VarChar(100),kp int, sumv decimal(10,2), srv decimal(10,5), srv1 decimal(10,5))

DECLARE cursor_rowguid CURSOR FOR
select distinct c.Name, COUNT(a.ProductID), SUM(a.Weight), AVG(a.Weight), SUM(a.Weight)/COUNT(a.ProductID) from Production.Product a
left join Production.ProductSubCategory b on a.ProductSubcategoryID=b.ProductSubcategoryID
left join Production.ProductCategory c on b.ProductCategoryID=c.ProductCategoryID
--where a.ProductSubcategoryID is not null
group by c.Name
OPEN cursor_rowguid
FETCH NEXT FROM cursor_rowguid
INTO @kat, @kp, @sumv, @srv, @srv1
WHILE @@FETCH_STATUS = 0
BEGIN	
	BEGIN 	
		Insert into #rez(kat,kp, sumv, srv, srv1) values (@kat, @kp, @sumv, @srv, @srv1)
	END 
	FETCH NEXT FROM cursor_rowguid
	INTO @kat, @kp, @sumv, @srv, @srv1
END
CLOSE cursor_rowguid
DEALLOCATE cursor_rowguid
select @kp=SUM(kp), @sumv=SUM(sumv), @srv=SUM(srv), @srv1=SUM(srv1) from #rez
Insert into #rez(kat,kp, sumv, srv, srv1) values ('Total', @kp, @sumv, @srv, @srv1)
SELECT kat as 'Категория',kp as 'Количество продуктов', sumv as 'Суммарный вес', srv as 'Средний вес', srv1 as 'Средний вес с учетом нулевых значений' FROM #rez
DROP TABLE #rez
go

select distinct c.Name, COUNT(a.ProductID), SUM(a.Weight), AVG(a.Weight), SUM(a.Weight)/COUNT(a.ProductID) from Production.Product a
left join Production.ProductSubCategory b on a.ProductSubcategoryID=b.ProductSubcategoryID
left join Production.ProductCategory c on b.ProductCategoryID=c.ProductCategoryID
--where a.ProductSubcategoryID is not null
group by c.Name
union
select 'Total', COUNT(a.ProductID), SUM(a.Weight), AVG(a.Weight), SUM(a.Weight)/COUNT(a.ProductID) from Production.Product a
left join Production.ProductSubCategory b on a.ProductSubcategoryID=b.ProductSubcategoryID
left join Production.ProductCategory c on b.ProductCategoryID=c.ProductCategoryID
--where a.ProductSubcategoryID is not null

;WITH T
AS (
  SELECT '1' Id, 2.0 V UNION 
  SELECT '2' Id, 3.0 V
)
SELECT Id,  V FROM T 
UNION ALL
SELECT 'Summary' Id , SUM(V) V FROM T

select case when grouping(c.Name) = 1 then 'Total' else c.Name end, COUNT(a.ProductID), SUM(a.Weight), AVG(a.Weight), SUM(a.Weight)/COUNT(a.ProductID)
from Production.Product a
left join Production.ProductSubCategory b on a.ProductSubcategoryID=b.ProductSubcategoryID
left join Production.ProductCategory c on b.ProductCategoryID=c.ProductCategoryID
group by grouping sets((), (c.Name));