sql

select t1.Name, t1.Osszmennyiseg, count(t2.RaktarakSzama1) as RaktarakSzama
from
    (select distinct ProductSubcategory.Name,
    SUM(ProductInventory.Quantity) as Osszmennyiseg
    --ROW_NUMBER() OVER(PARTITION BY ProductSubcategory.Name ORDER BY ProductInventory.LocationID DESC) as RaktarakSzama
    from Production.ProductSubcategory
    join Production.ProductCategory on ProductCategory.ProductCategoryID = ProductSubcategory.ProductCategoryID
    join Production.Product on ProductSubcategory.ProductSubcategoryID = Product.ProductSubcategoryID
    join Production.ProductInventory on ProductInventory.ProductID = Product.ProductID
    group by ProductSubcategory.Name) t1
join (select ProductSubcategory.Name,
    --COUNT(ProductSubcategory.Name) as RaktarakSzama
    ROW_NUMBER() OVER(PARTITION BY ProductSubcategory.Name ORDER BY ProductInventory.LocationID DESC) as RaktarakSzama1
    from Production.ProductSubcategory
    join Production.ProductCategory on ProductCategory.ProductCategoryID = ProductSubcategory.ProductCategoryID
    join Production.Product on ProductSubcategory.ProductSubcategoryID = Product.ProductSubcategoryID
    join Production.ProductInventory on ProductInventory.ProductID = Product.ProductID
    group by ProductSubcategory.Name,ProductInventory.LocationID ) t2
on t1.Name = t2.Name
group by t1.Name, t1.Osszmennyiseg