Highest Megacyte Percentage

From EVEDev

Items with highest concentration of megacyte in them.

Note: Replace megacyte with other minerals as needed

Note: SQL Server Syntax

 
SELECT
	CatMin.categoryID AS categoryID_Mineral, CatMin.categoryName AS categoryName_Mineral
	,GrpMin.groupID AS groupID_Mineral, GrpMin.groupName AS groupName_Mineral
	,TypMin.typeID AS typeID_Mineral, TypMin.typeName AS typeName_Mineral
	,Mat.quantity, MatAgg.SumQuantity
	,CONVERT(Numeric(9,6), CONVERT(Numeric(19,6), Mat.quantity)/CONVERT(Numeric(19,6), MatAgg.SumQuantity)*100.0) AS QuantityPercent
	,CatAst.categoryID AS categoryID_Asteroid, CatAst.categoryName AS categoryName_Asteroid
	,GrpAst.groupID AS groupID_Asteroid, GrpAst.groupName AS groupName_Asteroid
	,TypAst.typeID AS typeID_Asteroid, TypAst.typeName AS typeName_Asteroid
FROM
	dbo.invCategories AS CatAst
	JOIN dbo.invGroups AS GrpAst ON CatAst.categoryID=GrpAst.categoryID
	JOIN dbo.invTypes AS TypAst ON GrpAst.groupID=TypAst.groupID
	JOIN (
		SELECT MatAgg.typeID, SUM(MatAgg.quantity) AS SumQuantity
		FROM dbo.invTypeMaterials AS MatAgg
		GROUP BY MatAgg.typeID
	) AS MatAgg ON TypAst.typeID=MatAgg.typeID
	JOIN dbo.invTypeMaterials AS Mat ON TypAst.typeID=Mat.typeID
	JOIN dbo.invTypes AS TypMin ON Mat.materialTypeID=TypMin.typeID
	JOIN dbo.invGroups AS GrpMin ON TypMin.groupID=GrpMin.groupID
	JOIN dbo.invCategories AS CatMin ON GrpMin.categoryID=CatMin.categoryID
WHERE
	CatAst.categoryName='Asteroid'
	--(CatAst.categoryName='Asteroid' OR GrpAst.groupName='Refinables')
	--AND TypAst.typeName NOT LIKE 'Compressed%' AND TypAst.typeName NOT LIKE 'Fools%' AND TypAst.typeName NOT LIKE 'Flawed%'
	AND (GrpAst.groupName=TypAst.typeName OR GrpAst.groupName='Refinables')
	AND GrpMin.groupName='Mineral'
	AND TypMin.typeName='Megacyte'
ORDER BY 
	CatMin.categoryName, GrpMin.groupName, TypMin.typeName
	,CatAst.categoryName
	,QuantityPercent DESC
	,GrpAst.groupName, TypAst.typeName