Execute the following script in Query Editor to demonstrate the use of RIGHT JOIN to include count zero (not in table) items in a GROUP BY result.

use tempdb
go

select * into #NewProduct from AdventureWorks.Production.Product
go

delete #NewProduct where left(Name,1) in ('A','K', 'R', 'W')
go

select p.Name, count(np.Name)
from #NewProduct np
right join AdventureWorks.Production.Product p
on np.ProductID = p.ProductID
group by p.Name
order by p.Name
go