SELECT TOP (1000001) 
	[semijoin1].[c67],
	SUM([a0]) AS [a0]
FROM 
	(
		(
			SELECT 
				[t1].[ProductSubcategoryKey] AS [c29],
				COUNT_BIG(*) AS [a0]
			FROM 
			(
				(
					select 
						[$Table].[ProductKey] as [ProductKey],
						[$Table].[ProductAlternateKey] as [ProductAlternateKey],
						[$Table].[ProductSubcategoryKey] as [ProductSubcategoryKey],
						[$Table].[WeightUnitMeasureCode] as [WeightUnitMeasureCode],
						[$Table].[SizeUnitMeasureCode] as [SizeUnitMeasureCode],
						[$Table].[EnglishProductName] as [EnglishProductName],
						[$Table].[SpanishProductName] as [SpanishProductName],
						[$Table].[FrenchProductName] as [FrenchProductName],
						[$Table].[StandardCost] as [StandardCost],
						[$Table].[FinishedGoodsFlag] as [FinishedGoodsFlag],
						[$Table].[Color] as [Color],
						[$Table].[SafetyStockLevel] as [SafetyStockLevel],
						[$Table].[ReorderPoint] as [ReorderPoint],
						[$Table].[ListPrice] as [ListPrice],
						[$Table].[Size] as [Size],
						[$Table].[SizeRange] as [SizeRange],
						[$Table].[Weight] as [Weight],
						[$Table].[DaysToManufacture] as [DaysToManufacture],
						[$Table].[ProductLine] as [ProductLine],
						[$Table].[DealerPrice] as [DealerPrice],
						[$Table].[Class] as [Class],
						[$Table].[Style] as [Style],
						[$Table].[ModelName] as [ModelName],
						[$Table].[LargePhoto] as [LargePhoto],
						[$Table].[EnglishDescription] as [EnglishDescription],
						[$Table].[FrenchDescription] as [FrenchDescription],
						[$Table].[ChineseDescription] as [ChineseDescription],
						[$Table].[ArabicDescription] as [ArabicDescription],
						[$Table].[HebrewDescription] as [HebrewDescription],
						[$Table].[ThaiDescription] as [ThaiDescription],
						[$Table].[GermanDescription] as [GermanDescription],
						[$Table].[JapaneseDescription] as [JapaneseDescription],
						[$Table].[TurkishDescription] as [TurkishDescription],
						[$Table].[StartDate] as [StartDate],
						[$Table].[EndDate] as [EndDate],
						[$Table].[Status] as [Status]
					from [dbo].[DimProduct] as [$Table]
				)
			) AS [t1]
		GROUP BY [t1].[ProductSubcategoryKey]
	) AS [basetable0]
inner join 
	(
		(SELECT N'Mountain Bikes' AS [c67],1 AS [c29] )  UNION ALL 
		(SELECT N'Road Bikes' AS [c67],2 AS [c29] )  UNION ALL 
		(SELECT N'Touring Bikes' AS [c67],3 AS [c29] )  UNION ALL 
		(SELECT N'Handlebars' AS [c67],4 AS [c29] )  UNION ALL 
		(SELECT N'Bottom Brackets' AS [c67],5 AS [c29] )  UNION ALL 
		(SELECT N'Brakes' AS [c67],6 AS [c29] )  UNION ALL 
		(SELECT N'Chains' AS [c67],7 AS [c29] )  UNION ALL 
		(SELECT N'Cranksets' AS [c67],8 AS [c29] )  UNION ALL 
		(SELECT N'Derailleurs' AS [c67],9 AS [c29] )  UNION ALL 
		(SELECT N'Forks' AS [c67],10 AS [c29] )  UNION ALL 
		(SELECT N'Headsets' AS [c67],11 AS [c29] )  UNION ALL 
		(SELECT N'Mountain Frames' AS [c67],12 AS [c29] )  UNION ALL 
		(SELECT N'Pedals' AS [c67],13 AS [c29] )  UNION ALL 
		(SELECT N'Road Frames' AS [c67],14 AS [c29] )  UNION ALL 
		(SELECT N'Saddles' AS [c67],15 AS [c29] )  UNION ALL 
		(SELECT N'Touring Frames' AS [c67],16 AS [c29] )  UNION ALL 
		(SELECT N'Wheels' AS [c67],17 AS [c29] )  UNION ALL 
		(SELECT N'Bib-Shorts' AS [c67],18 AS [c29] )  UNION ALL 
		(SELECT N'Caps' AS [c67],19 AS [c29] )  UNION ALL 
		(SELECT N'Gloves' AS [c67],20 AS [c29] )  UNION ALL 
		(SELECT N'Jerseys' AS [c67],21 AS [c29] )  UNION ALL 
		(SELECT N'Shorts' AS [c67],22 AS [c29] )  UNION ALL 
		(SELECT N'Socks' AS [c67],23 AS [c29] )  UNION ALL 
		(SELECT N'Tights' AS [c67],24 AS [c29] )  UNION ALL 
		(SELECT N'Vests' AS [c67],25 AS [c29] )  UNION ALL 
		(SELECT N'Bike Racks' AS [c67],26 AS [c29] )  UNION ALL 
		(SELECT N'Bike Stands' AS [c67],27 AS [c29] )  UNION ALL 
		(SELECT N'Bottles and Cages' AS [c67],28 AS [c29] )  UNION ALL 
		(SELECT N'Cleaners' AS [c67],29 AS [c29] )  UNION ALL 
		(SELECT N'Fenders' AS [c67],30 AS [c29] )  UNION ALL 
		(SELECT N'Helmets' AS [c67],31 AS [c29] )  UNION ALL 
		(SELECT N'Hydration Packs' AS [c67],32 AS [c29] )  UNION ALL 
		(SELECT N'Lights' AS [c67],33 AS [c29] )  UNION ALL 
		(SELECT N'Locks' AS [c67],34 AS [c29] )  UNION ALL 
		(SELECT N'Panniers' AS [c67],35 AS [c29] )  UNION ALL 
		(SELECT N'Pumps' AS [c67],36 AS [c29] )  UNION ALL 
		(SELECT N'Tires and Tubes' AS [c67],37 AS [c29] )  UNION ALL 
		(SELECT null AS [c67],null AS [c29] ) 
	) AS [semijoin1] 
	on [semijoin1].[c29] = [basetable0].[c29]
)
GROUP BY [semijoin1].[c67] 