fitment-count-year-make-model.sql

select	StartYear
		, EndYear
		, Make
		, Model
		, COUNT(f.Id) as [FitmentCount]
from	ItmFitments f join ItmItems i on f.ItemId = i.Id
		JOIN ItmGroups g on i.GroupId = g.Id
		JOIN ItmBrands b on g.BrandId = b.Id
where	Make != 'UNIVERSAL'
		AND Make != 'NON-US VEHICLE'
		AND i.IsActive = 1
		AND g.IsActive = 1
		AND b.IsActive = 1
		AND b.ClientId = 1
group by
		StartYear
		, EndYear
		, Make
		, Model
having	COUNT(f.Id) > 0 -- adjust 0 to N to filter by FitmentCount
order by
		Make
		, Model
		, StartYear
		, EndYear

fitment-counts.sql

-- ALL FITMENTS
SELECT	COUNT(f.Id) as [Count]
from	ItmFitments f join ItmItems i on f.ItemId = i.Id
		JOIN ItmGroups g on i.GroupId = g.Id
		JOIN ItmBrands b on g.BrandId = b.Id
where	Make != 'UNIVERSAL'
		AND Make != 'NON-US VEHICLE'
		AND i.IsActive = 1
		AND g.IsActive = 1
		AND b.IsActive = 1

-- Makes & Models
select	Make
		, Model 
		, COUNT(f.Id) as [Count]
from	ItmFitments f join ItmItems i on f.ItemId = i.Id
		JOIN ItmGroups g on i.GroupId = g.Id
		JOIN ItmBrands b on g.BrandId = b.Id
where	Make != 'UNIVERSAL'
		AND Make != 'NON-US VEHICLE'
		AND i.IsActive = 1
		AND g.IsActive = 1
		AND b.IsActive = 1
group by 
		Make
		, Model
order by 
		COUNT(*) DESC

-- DISTINCT FITMENTS
SELECT	[StartYear]
		,[EndYear]
		,[Make]
		,[Model]
		,[SubModel]
		,[Cyl]
		,[Lit]
		,[Fuel]
		,[FuelDel]
		,[ASP]
		,[EngDesg]
		--,[Notes]
		, COUNT(*) as Count
from	ItmFitments f join ItmItems i on f.ItemId = i.Id
		JOIN ItmGroups g on i.GroupId = g.Id
		JOIN ItmBrands b on g.BrandId = b.Id
where	Make != 'UNIVERSAL'
		AND Make != 'NON-US VEHICLE'
		AND i.IsActive = 1
		AND g.IsActive = 1
		AND b.IsActive = 1
GROUP BY
		[StartYear]
		,[EndYear]
		,[Make]
		,[Model]
		,[SubModel]
		,[Cyl]
		,[Lit]
		,[Fuel]
		,[FuelDel]
		,[ASP]
		,[EngDesg]
		--,[Notes]
ORDER BY
		--COUNT(*) DESC
		[Make]
		, [Model]
		, [SubModel]