Talk:Create Table with Planet Type Counts in Each System

From EVEDev

I suggest this alternative query, only takes 1-2 secs instead of 45.

SELECT m.solarSystemID,
COUNT(*) AS total,
SUM(IF(m.typeID=2016,1,0)) AS barren,
SUM(IF(m.typeID=13,1,0)) AS gas,
SUM(IF(m.typeID=12,1,0)) AS ice,
SUM(IF(m.typeID=2015,1,0)) AS lava,
SUM(IF(m.typeID=2014,1,0)) AS oceanic,
SUM(IF(m.typeID=2063,1,0)) AS plasma,
SUM(IF(m.typeID=30889,1,0)) AS shattered,
SUM(IF(m.typeID=2017,1,0)) AS storm,
SUM(IF(m.typeID=11,1,0)) AS temperate
FROM mapDenormalize AS m
WHERE m.typeID IN(2016,13,12,2015,2014,2063,30889,2017,11)
GROUP BY m.solarSystemID