Execute the following Microsoft SQL Server Transact-SQL (T-SQL) script in Management Studio (SSMS) Query Editor, SQLCMD or other client software 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